Dax Measure with related Table

[coolshib]

Hello Everyone,

I am looking for a dax measure function based on the following logic.

I have three tables,

Table No.1

Category           Online Price         Offline Price

Laptop                  38000                   42000

TV                         45000                   50000

Mobile                  15000                   17000

Microwave            20000                   23000

Table No.2

Product Name      Category

Lenovo                  Laptop

Sony Bravia           TV

Samsung               Mobile

LG                          Microwave

Table No.3

Product Name       Qnty      Mode       Total Sales Amount (a dax measure not a column)

Lenovo                      3        Online            ?

Sony Bravia               4        Offline            ?

Samsung                   5        Online            ?

LG                              2        Offline           ?

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

[ChandeepChhabra · Accepted solution]

Hi @coolshib,

Please establish a relationship between 3 tables and try the following measure for

Total Sales =SUMX(Table3,IF(Table3[Mode]="Online",RELATED(Table1[Online Price]),RELATED(Table1[Offline Price]))*Table3[Qty])

Here is the snapshot of the output

You can download the Power Pivot file from here

Hope it helps

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[coolshib]

Thank you so much.. Great Help.

Best Regards

Shib

[Ashish_Mathur]

Hi,

If my reply helped, please mark it as Answer.

[Ronald123]

Hi @Ashish_Mathur,

Why the SUMX formule >

Total sales = SUMX(SUMMARIZE(Table1;Products[Product Name];'Mode of payment'[Mode];"ABCD";[Price per unit]*[Quantity sold]);[ABCD])

If this formule give the same results.

Total Sales2 = [Quantity sold]*[Price per unit]

Greets,

Ronald

[Ashish_Mathur]

Hi,

Your measure would give the correct row wise totals but the incorrect grand total.

[Ronald123]

Hi @Ashish_Mathur,

This one gives me also the correct grand total.

Total sales = SUMX(Table1;[Price per unit]*[Quantity sold])

I will understand wy to generate a virtual table with summarize.

Greets,

Ronald

[Ashish_Mathur]

Yes, you are right.  We do not need to create a virtual Table.  Thank you.

[coolshib]

Hi Ashish ( @Ashish_Mathur ),

i have another query based on a similar situation. What if i have multiple mode of payments catrgorised under these two Payment Categories. For example, my Table 1 and Table 2 will remain same. There wont be any changes. if i would have a 4th table as follows

Table No.4

Type of Payment      Mode

Cash Payment           Offline

Gift Card                   Offline

Cheque Payment      Offline

Credit Card               Online

Debit Card                Online

UPI                            Online

Then my Table No.3 would revised as follows

Table No.3

Product Name       Qnty      Mode       Total Sales Amount (a dax measure not a column)

Lenovo                      3        Cheque                  ?

Sony Bravia               4        Gift Card                ?

Samsung                   5        Credit Card            ?

LG                              2        UPI                        ?

Thank you so much for your valuable reply.

Best Regards

Shib

[Ashish_Mathur]

Hi,

In Table 1 and Table 3 as well, you will have to clarify the Type of payment.  If that information can be shared, then i can edit my solution to match your requirement.

[ChandeepChhabra · Accepted solution]

Hi @coolshib,

Please establish a relationship between 3 tables and try the following measure for

Total Sales =SUMX(Table3,IF(Table3[Mode]="Online",RELATED(Table1[Online Price]),RELATED(Table1[Offline Price]))*Table3[Qty])

Here is the snapshot of the output

You can download the Power Pivot file from here

Hope it helps

[venug20]

@ChandeepChhabra

superb... solution... very good smart effort.... thanks for reply...

[coolshib]

Thank you Mr.Chhabra ( @ChandeepChhabra ) for the solution. It works like a charm.

I have one more query regarding this issue, what if i have more than two modes of payment like "Online Transfer, Cash Payment, Credit Card, Debit Card" etc instead of "Offline & Online only".

Thank you so much for your promt reply.

Best Regards

Shib

[ChandeepChhabra]

You can establish a relation between Table1 and Table 3 with a bit of modification using Power Query

Please download the power bi file

[venug20]

@coolshib

Is it ok with the below output... 

[coolshib]

Thank you so much for your reply @venug20.
Actually i am looking for dax measure which would return the value in a single column.
Also i dont want to add or delete any column from Table No.3 as mentioned above. The format will remain the same. In your solution you have added the category column in the dataset which i don't want.
Best Regards
Shib

[venug20]

@coolshib

I am trying to display same column calculation field (Online, offline). till not achieve..

you can try inthe mean while, i will provide dax formula which i has got upto till now....

Online Sales = CALCULATE(SUM('Product'[Sales.Quantity]) * SUM('Product-Price'[Online Price]), FILTER('Product', 'Product'[Category] = RELATED('Product-Price'[Category]) && 'Product'[Sales.Mode] = "Online")) 

Offline Sales = CALCULATE(SUM('Product'[Sales.Quantity]) * SUM('Product-Price'[Offline Price]), FILTER('Product', 'Product'[Category] = RELATED('Product-Price'[Category]) && 'Product'[Sales.Mode] = "Offline"))