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                  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
 
Need Help

16 Replies

  • ChandeepChhabra's avatar
    ChandeepChhabra
    Icon for Impactful Individual rankImpactful Individual
    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

     

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

      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

    • coolshib's avatar
      coolshib
      Icon for Helper III rankHelper III
      7 years ago
      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's avatar
        venug20
        Icon for Resolver I rankResolver I
        7 years ago

        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"))

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

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

      Thank you so much.. Great Help.

      Best Regards

      Shib