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.

     

venug20's avatar
venug20
Icon for Resolver I rankResolver I
7 years ago

coolshib

 

Is it ok with the below output... 

 

 

 

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