Forum Discussion

VikramAdi's avatar
VikramAdi
Helper II
3 years ago

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

 

tamerj1 Greg_Deckler some_bih Jihwan_Kim johnt75 

eliasayyy Alf94 devanshi Mahesh0016 barritown 

@Dax
@Dax help
@measure
@NeedHelp

5 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    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

     

  • some_bih's avatar
    some_bih
    Community Champion
    3 years ago

    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
  • VikramAdi's avatar
    VikramAdi
    Helper II
    3 years ago

    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's avatar
      some_bih
      Community Champion
      3 years ago

      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