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

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,

     

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

      Hi,

       

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

      • 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

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

    Thank you so much.. Great Help.

    Best Regards

    Shib

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

      Hi,

       

      If my reply helped, please mark it as Answer.