Calculating Difference between data in a table with two seperate ID keys

[sirgseymour]

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

[v-lili6-msft]

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

[sirgseymour]

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

[Greg_Deckler]

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.

[sirgseymour]

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)

[v-lili6-msft]

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

[sirgseymour]

Thank you for your response. I think this shows the crux of the issue. In my data there are more than two portfolios (there are several hundred) What I don't know how to do is to make the two Portfolio selections in the slicers and then read those values into the formula that you have below

Measure = CALCULATE(SUM(Table4[Aggregate Loss]),Table4[Portfolio]=<Value from slicer 1>)-CALCULATE(SUM(Table4[Aggregate Loss]),Table4[Portfolio]=<Value from slicer 2>)

It is the ability to select the values from the individual slicers that I am having issues with.

THank you for helping me clarify my request!!!!

[v-lili6-msft · Accepted solution]

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.

https://www.dropbox.com/s/fq6p0obgxbg2ai8/Calculating%20Difference%20between%20data%20in%20a%20table...

Best Regards,

Lin