Forum Discussion

webe0436's avatar
webe0436
New Member
9 months ago
Solved

How to add row to matrix calculating difference between two values ?

Hi, My goal is to compare the forecast of two teams across fiscal years. I added their forecasted revenue to a matrix as shown in the screenshot below: My goal is to add a third row that ...
Need Help
  • Praful_Potphode's avatar
    Praful_Potphode
    9 months ago

    Hi webe0436 

     

    Forecast Revenue = SUM(YourTable[ForecastRevenue]) 
    Team A Revenue = CALCULATE([Forecast Revenue],YourTable[ForecastTeam]="forecast team a") 
    Team B Revenue = CALCULATE([Forecast Revenue],YourTable[ForecastTeam]="forecast team b") 
    Difference FOrecast = [Team A Revenue]-[Team B Revenue]

    The create a matrix as show below.

    turn switch values to rows on.

    Sample pBIX can be downloaded from here.

    please give kudos or mark it as solution once confirmed.

     

    Thanks and Regards,

    Praful

     

     

     

     

danextian's avatar
danextian
Super User
9 months ago

Hi webe0436 

You can simply tell DAX to return the difference between A and B at the total row assuming there are only two forecast teams.

IF (
    NOT ( HASONEVALUE ( 'table'[Forecast Team] ) ),
    CALCULATE ( [forecast], 'table'[Forecast Team] = "A" )
        - CALCULATE ( [forecast], 'table'[Forecast Team] = "B" ),
    [forecast]
)