Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
VikramAdi
Helper II
Helper II

How to calculate differnce between 2 comaprision table columns

VikramAdi_0-1688023048955.png

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 

5 REPLIES 5
tamerj1
Super User
Super User

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

1.png2.png

 

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
Helper II
Helper II

Can anyone  help me here to achive expected result.

 

VikramAdi
Helper II
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.

VikramAdi_0-1688039477250.png

 

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.

 

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!






some_bih
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.

 

some_bih_0-1688038071254.png

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!






Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors