Measure with data from different tables

[jayala]

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 purchases data sorted (Date, Product Description, Unit Price, Quantity) by date and I need to compare/analyze two different periods of time for the same products. If I use the same dataset I do not know how to implement two different slicers for each period selection so I created two different tables with all the data duplicated and individual date-slicers.

Currently, I am using two different matrices as visuals to see the data. Since it is a evolution analysis I need to build some measures based on values with the individual data of each period of time (quantity in period 1 and unit price in period 2).

My first question is if exists a way to join the data showed in each matrix when the filters are applied and then use that new table.

Second, if not: can I build a measure using columns from different tables even if they do not have a relationship (as this case) and when they are being filtered?

I really appreciate your help guys, I am a begginer in PowerBI but I have learnt a lot in this community.

Thanks in advance!

[v-diye-msft]

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]

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 

[v-diye-msft]

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]

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 · Accepted solution]

Hi,

See if my solution in this file helps.

[Ashish_Mathur]

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.

[Ashish_Mathur]

Hi,

Share a dataset, explain the business context and show the expected result.