Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
jayala
Frequent Visitor

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 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!

1 ACCEPTED SOLUTION

Hi,

See if my solution in this file helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

7 REPLIES 7
v-diye-msft
Community Support
Community Support

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.

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

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 

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?

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

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

Hi,

See if my solution in this file helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Ashish_Mathur
Super User
Super User

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.