If statement Dax function as a measure

[spycein]

Hello Everyone,
I am looking for a dax measure function based on the following logic
i have three tables,

Table No.1

[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD][TABLE="width: 110"]
<tbody>[TR]
[TD="width: 110"]Category[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="width: 77"]Online Price[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="width: 77"]Offline Price[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 110"]
<tbody>[TR]
[TD="width: 110"]Laptop[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]38000[/TD]
[TD]42000[/TD]
[/TR]
[TR]
[TD][TABLE="width: 110"]
<tbody>[TR]
[TD="width: 110"]TV[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]45000[/TD]
[TD]50000[/TD]
[/TR]
[TR]
[TD][TABLE="width: 110"]
<tbody>[TR]
[TD="width: 110"]Mobile[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]15000[/TD]
[TD]17000[/TD]
[/TR]
[TR]
[TD][TABLE="width: 110"]
<tbody>[TR]
[TD="width: 110"]Microwave[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]20000[/TD]
[TD]23000[/TD]
[/TR]
</tbody>[/TABLE]

Table No.2

[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD][TABLE="width: 91"]
<tbody>[TR]
[TD="width: 91"]Product Name[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 110"]
<tbody>[TR]
[TD="width: 110"]Category[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 91"]
<tbody>[TR]
[TD="width: 91"]Lenovo[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 110"]
<tbody>[TR]
[TD="width: 110"]Laptop[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 91"]
<tbody>[TR]
[TD="width: 91"]Sony Bravia[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 110"]
<tbody>[TR]
[TD="width: 110"]TV[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 91"]
<tbody>[TR]
[TD="width: 91"]Samsung[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 110"]
<tbody>[TR]
[TD="width: 110"]Mobile[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 91"]
<tbody>[TR]
[TD="width: 91"]LG[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 110"]
<tbody>[TR]
[TD="width: 110"]Microwave[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Table No.3

[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD][TABLE="width: 110"]
<tbody>[TR]
[TD="width: 110"]Product Name[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="width: 77"]Qnty[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="width: 77"]Mode[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 98"]
<tbody>[TR]
[TD="width: 98"]Total Sale Amount ( A Dax Measure not a column)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 110"]
<tbody>[TR]
[TD="width: 110"]Lenovo[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]3[/TD]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="width: 77"]Online[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD] ? [/TD]
[/TR]
[TR]
[TD][TABLE="width: 110"]
<tbody>[TR]
[TD="width: 110"]Sony Bravia[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]4[/TD]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="width: 77"]Offline[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD] ?[/TD]
[/TR]
[TR]
[TD][TABLE="width: 110"]
<tbody>[TR]
[TD="width: 110"]Samsung[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]5[/TD]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="width: 77"]Online[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD] ?[/TD]
[/TR]
[TR]
[TD][TABLE="width: 110"]
<tbody>[TR]
[TD="width: 110"]LG[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]2[/TD]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="width: 77"]Offline[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD] ?[/TD]
[/TR]
</tbody>[/TABLE]

I am looking a dax function as a measure which would return the total sold price amount.
I dont want to add a column or create another separate table.
Thank you so much.
Best Regards
Shib

 

[Matt Allington]

Why is your pricing at the category level and not the product level? This implies that all products in a category are always the same price instead of each product in a category having its own price. This sounds wrong to me. regardless, I suggest you merge table 1 and 2 I to a single table on data load (I will call it table 4). You can use power query for this. Then join the new table to table 3 with a 1 to many relationship (table4 to table 3) on product name.
The DAX is a bit tricky, but a variable syntax measure should help. This will need to be tested but I think it is right.


Result =
SUMX (
table4,
VAR offlineprice = table4[offline]
VAR onlineprice = table3[online]
RETURN
CALCULATE (
VAR mode =
SELECTEDVALUE ( table3[mode] )
RETURN
SWITCH ( mode, "online", onlineprice, "offline", offlineprice, 0 )
* SUM ( table3[qty] )
)
)