Forum Discussion

jayala's avatar
jayala
Frequent Visitor
6 years ago
Solved

Measure with data from different tables

Hi guys,   I am experiencing some problems creating measures with data from different tables. I spent lot of time looking for a solution and I hope you can help me.   I have a dataset with purcha...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    6 years ago

    Hi,

    See if my solution in this file helps.

v-diye-msft's avatar
v-diye-msft
Community Support
6 years ago

Hi jayala 

 

Sorry I don't get your requirement, what's the logic in the measures you mentioned? "Period 1 Spend", "Volume", "Price", "Mix" and "Period 2 Spend"?

Would you mind sharing your calculation logic and expected results that we can repro it?

 

jayala's avatar
jayala
Frequent Visitor
6 years ago

Hi guys,


As Ashish_Mathur requested I uploaded the raw data source for the PBI file.

The two tables are the same, I just duplicated the tables to use two different slicers.


Excel Raw Data 


@v-diye-msftthe formulas used are in the excel file, I listed below:


- Period 1 Spend = sum(Period1([Net Price])

- Volume = (sum(Period2[Quantity])-sum(Period1[Quantity]))*sum(Period1[Net Price])/sum(Period1[Quantity])

- Price = (sumproduct(Period1[Quantity], Period2[Unit Price])/sum(Period1[Quantity])-sum(Period1[Net Price])/sum(Period1[Quantity]))*sum(Period2[Quantity])

- Period 2 Spend = sum(Period2([Net Price])

- Mix = Period 2 Spend - Price - Volume - Period 1 Spend


I hope you can understand. If not, just let me know.

Thank you guys

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    6 years ago

    Hi,

    See if my solution in this file helps.