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.

     

Ronald123's avatar
Ronald123
Icon for Resolver III rankResolver III
7 years ago

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'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.