Forum Discussion
Helper IIIDax Measure with related Table
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
Resolver IIIHi 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
Super UserHi,
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
