Forum Discussion
Calculating Difference between data in a table with two seperate ID keys
I am trying to compare portfolio data that exists in a single table. For Example, I have the data for two Portfolios containing losses in the same table. I have created two Tables in the Power BI UI and want to create a third that shows the difference between the two. What I want to do is do the calculation:
Difference= (Sum Aggregate values where PortfolioID=1) - (Sum Aggregate values where PortfolioID=2)
I need to be able to select the Portfolio ID's from slicers to see each of the portfolios in the two tables... Any ideas Thanks
hi,@sirgseymour
After my research, I'm afraid it couldn't achieve in Power BI in one table, but you can do these as below:
Step1:
use this formula to duplicate your basic table
Table 2 = 'Table 1'
Step2:
Use Values Function to create a Country fact table:
Country = VALUES('Table 1'[Country] )Step3:
Create the relationship between them
Step4:
Use ALLSELECTED Function to create a measure
difference = CALCULATE(SUM('Table 1'[Aggregate Loss]),ALLSELECTED('Table 1'[Portfolio]))-CALCULATE(SUM('Table 2'[Aggregate Loss]),ALLSELECTED('Table 2'[Portfolio]))Step5:
Drag fields into visual like this:
here is pbix, please try it.
Best Regards,
Lin
7 Replies
I am trying to compare portfolio data that exists in a single table. For Example, I have the data for two Portfolios containing losses in the same table. I have created two Tables in the Power BI UI and want to create a third that shows the difference between the two. What I want to do is do the calculation:
Difference= (Sum Aggregate values where PortfolioID=1) - (Sum Aggregate values where PortfolioID=2)
I need to be able to select the Portfolio ID's from slicers to see each of the portfolios in the two tables... Any ideas Thanks
Tough to be specific without sample data. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
That being said, you could create a measure that starts with two VAR's that do a MIN and MAX of your slicer. You could then use that to calculate your difference by filtering based upon your variables.
Again, tough to be specific here.
Thank you for getting back to me. Here is an example that i have put together to give you an idea of what I am looking to do. In the example below I have included a Data ttable (SQL Table) and the three tables I want to show inthe UI. What I want to be able to do is select the individual Portfolios in the slicers and then subtract the aggregated data from the individual tables.
Data Table Portfolio Country Aggregate Loss Portfolio 1 Argentina 50,000,000 Portfolio 1 US 50,000,000 Portfolio 1 UK 50,000,000 Portfolio 1 Germany 50,000,000 Portfolio 1 Argentina 60,000,000 Portfolio 1 US 60,000,000 Portfolio 1 UK 60,000,000 Portfolio 1 Germany 60,000,000 Portfolio 2 Argentina 80,000,000 Portfolio 2 US 80,000,000 Portfolio 2 UK 80,000,000 Portfolio 2 Germany 80,000,000 Portfolio 2 Argentina 110,000,000 Portfolio 2 US 110,000,000 Portfolio 2 UK 110,000,000 Portfolio 2 Germany 110,000,000 Power BI Visual Portfolio 1 (Slicer) Country Aggregate Loss Argentina 110,000,000 US 110,000,000 UK 110,000,000 Germany 110,000,000 Portfolio 2 (Slicer) Country Aggregate Loss Argentina 190,000,000 US 190,000,000 UK 190,000,000 Germany 190,000,000 Difference Country Aggregate Loss Argentina (80,000,000) US (80,000,000) UK (80,000,000) Germany (80,000,000) hi,@sirgseymour
After my research, whether there are only two types in column Portfolio, you can use this formula:
Measure = CALCULATE(SUM(Table4[Aggregate Loss]),Table4[Portfolio]="Portfolio 1")-CALCULATE(SUM(Table4[Aggregate Loss]),Table4[Portfolio]="Portfolio 2")
if there are more than two types in column Portfolio,
when you select Portfolio 1 in slicer,
first table visual will show data of Portfolio 1 and second table visual will show data of Portfolio 1 too.
And when you select Portfolio 2 in slicer,
second table visual will show data of Portfolio 2 and first table visual will show data of Portfolio 2 too.
Best Regards,
Lin
hi, sirgseymour
Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?
Best Regards,
Lin
