cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper II

## How to calculate differnce between 2 comaprision table columns

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

5 REPLIES 5
Super User

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

Helper II

Can anyone  help me here to achive expected result.

Helper II

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.

Super User

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

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Super User

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

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors