How to calculate differnce between 2 comaprision table columns

[VikramAdi]

Table 1 and table 2 currently using for comapre same data at a time, using edit interctions, i stoped interctions with table 1 and table 2. now i am able to compare data.

I am having another requirment on same page, user want to know differnce between both the comparison tables.(On selected slicers )

when user selects  first table related slicers substract with  2nd table slicers data and display on new table.

Can anyone help me how to achive this, atleast for 1 metric, that helps.( Need to know how to write a measure to achive output)

Please find following url to access the test data dashboard. https://drive.google.com/file/d/15nyVZc5WkvrMoYWCbgXdvufYUMBeETZO/view?usp=sharing

@tamerj1 @Greg_Deckler @some_bih @Jihwan_Kim @johnt75 

@eliasayyy @Alf94 @devanshi @Mahesh0016 @barritown 

[tamerj1]

Hi @VikramAdi 
This can never be achieved with a single table data model. Please refer to attached sample file with the proposed solution

Diff Quantity = 
VAR Value1 = CALCULATE ( SUM ( Data[Quantity] ), ALL ( Scenario1 ), ALL ( Period2 ) )
VAR Value2 = CALCULATE ( SUM ( Data[Quantity] ), ALL ( Scenario2 ), ALL ( Period2 ) )
RETURN
    Value2 - Value1

Diff Sold = 
VAR Value1 = CALCULATE ( SUM ( Data[Sold] ), ALL ( Scenario1 ), ALL ( Period2 ) )
VAR Value2 = CALCULATE ( SUM ( Data[Sold] ), ALL ( Scenario2 ), ALL ( Period2 ) )
RETURN
    Value2 - Value1

Diff Amount = 
VAR Value1 = CALCULATE ( SUM ( Data[Amount] ), ALL ( Scenario1 ), ALL ( Period2 ) )
VAR Value2 = CALCULATE ( SUM ( Data[Amount] ), ALL ( Scenario2 ), ALL ( Period2 ) )
RETURN
    Value2 - Value1

[VikramAdi]

Can anyone  help me here to achive expected result.

[VikramAdi]

Thanks for your responce @some_bih ,

My power BI report page contains same slicers for table 1 and same slicers for table 2. i took duplicate and i stop intercation.

we need to derive a measure based on slection of all slicers.

FYI- Scenario slicer having multiple values(Actual,Fcast,Budget), Year slicer having multiple values(2023,2022,2021) and same for period.

[some_bih]

Hi @VikramAdi sure, still in your single table everything is there, so you basically do not need to do something special only slice other solution would leave to blank results for some filters combination

[some_bih]

Hi @VikramAdi you can create 3 measures as shown below, and organize table as shown on picture below.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Diff Quantity =

VAR _actual=CALCULATE( SUM(Data[Quantity]),Data[Scenario]="Actual")

VAR _FC=CALCULATE( SUM(Data[Quantity]),Data[Scenario]="Fcast")

VAR _result=_actual-_FC

RETURN _result

Diff Sold =

VAR _actual=CALCULATE( SUM(Data[Sold]),Data[Scenario]="Actual")

VAR _FC=CALCULATE( SUM(Data[Sold]),Data[Scenario]="Fcast")

VAR _result=_actual-_FC

RETURN _result

Diff Amount =

VAR _actual=CALCULATE( SUM(Data[Amount]),Data[Scenario]="Actual")

VAR _FC=CALCULATE( SUM(Data[Amount]),Data[Scenario]="Fcast")

_result=_actual-_FC

RETURN _result