Forum Discussion

coolshib's avatar
coolshib
Icon for Helper III rankHelper III
7 years ago
Solved

Dax Measure with related Table

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       ...
Need Help
  • ChandeepChhabra's avatar
    ChandeepChhabra
    7 years ago

    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's avatar
    Ashish_Mathur
    7 years ago

    Hi,

     

    You may download my PBI file from here.

     

    Hope this helps.

     

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
7 years ago

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

coolshib's avatar
coolshib
Icon for Helper III rankHelper III
7 years ago

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's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    7 years ago

    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.