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 

 

You might consider creating pbix file that will contain some sample data (remove the confidential info), upload the pbix to onedrive or dropbox and share the link to the file. Please do not forget to describe the expected results based on this sample data.

 

jayala's avatar
jayala
Frequent Visitor
6 years ago

Hi guys Ashish_Mathur, v-diye-msft 

 

Here I am sharing a excel file with the analysis that I need to replicate in PowerBI.

 

Given two tables filtered by date ("Period 1" and "Period 2")  I need to build the 5 measures of the bottom table: ("Period 1 Spend", "Volume", "Price", "Mix" and "Period 2 Spend"). For the first 2 I built measures in PBI but for "Price" I could not. "Price" has something like a "dot product" between Quantity and price columns in period 1 and period 2, respectively that I am not able to reproduce.

 

Thank you again guys, you helped me a lot!

 

Excel Sample Data 

pbix Sample Data 

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    6 years ago

    Hi,

    I am assuming the only difference between the 2 raw datasets is the period that they belong to.  Am i correct?  If yes, then also share the raw Excel files from where you uploaded data to the PBI file.

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