Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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!
Solved! Go to Solution.
Hi,
See if my solution in this file helps.
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.
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!
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?
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.
@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.
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.
Hi,
Share a dataset, explain the business context and show the expected result.
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
72 | |
70 | |
37 | |
29 | |
26 |
User | Count |
---|---|
91 | |
49 | |
45 | |
38 | |
36 |