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

[webe0436]

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 simply subtract the one row from the other to show the difference in a third row, but am unable to do so. The values come from a table column named "value", which also includes unit forecasts. This matrix filters on revenue, specifically. 

Any help would be greatly appreciated, thanks!

[danextian]

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]
)

[Praful_Potphode · Accepted solution]

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

[webe0436]

This worked great and was very quick, thank you!

[Ashish_Mathur]

Hi,

Share the download link of the PowerBI file.

[Zanqueta]

Hi @webe0436,

 
This requires a DAX Measure that explicitly overrides the current row filter context within the matrix. Since "Forecast team A" and "Forecast team B" are values within a column, we must use the CALCULATE function to isolate and sum the values for each team, allowing the final measure to perform the subtraction regardless of which team's row it is placed in (or in a new total row).

You need to replace 'YourTable'[Forecast Team Column] with the actual name of the column that contains the team names (e.g., [Team Name]).

try this:

Forecast Difference (A - B) =
VAR TeamA_Value =
    CALCULATE(
        SUM('YourTable'[Value]),
        'YourTable'[Forecast Team Column] = "Forecast team A"
    )
VAR TeamB_Value =
    CALCULATE(
        SUM('YourTable'[Value]),
        'YourTable'[Forecast Team Column] = "Forecast team B"
    )
RETURN
    TeamA_Value - TeamB_Value

Instructions for Use

1. Create the Forecast Difference (A - B) measure.

2. Drag this new measure into the "Values" field well of your matrix visual.

This measure will automatically appear as a separate total or row in the matrix (depending on your matrix settings) showing the difference for each fiscal year.

---

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly. Appreciate your KUDO if you find it useful.

[webe0436]

Thank you for the reply. I followed your steps, as seen in my measure below:

However, when dragging the measure into the values section of the matrix, it adds the measure as an additional column within the same rows:

For other reasons, this needs to be its own row. Is that possible? Thanks in advance!

[Zanqueta]

Hello @webe0436,

Here I think we will have a situation. PowerBI behaves differently from standard Excel. This visual is closer to a pivot table, think with me, this wouldn't be easy to implement there, right? I also understand that sometimes this is the requirement from the business team who would like to see things the same way as before. In my opinion, it's a matter of adjusting the matrix layout and it's resolved. Another way to do it would involve virtual tables and a slightly more advanced DAX, which in day-to-day use I'm not sure how maintenance would be handled, you understand? 

Alternative Solution: Using a DAX Calculated Table (Union)

This is the cleanest and most robust solution to force a custom calculation to appear as a regular category row, but it requires permission to create a Calculated Table in your Power BI Desktop model (which is possible even with many Live Connections, depending on the source).

The strategy is to create a new table (Forecast Extended) that appends the calculated difference values as a new category line.

Step 1: Create the Calculated Table

You will need two key measures within this calculated table: one to calculate the original teams' values and another to summarize and calculate the difference.

Forecast Extended =
UNION(
    -- 1. Original Data (Team A and Team B)
    SELECTCOLUMNS(
        'YourTable',
        "Team Type", 'YourTable'[Forecast Team Column],
        "Fiscal Year", 'YourTable'[Fiscal Year Column], -- Replace with your actual year column
        "Value", 'YourTable'[Value]
    ),
    -- 2. Difference Row (Calculated Summary)
    SUMMARIZE(
        'YourTable',
        'YourTable'[Fiscal Year Column], -- Group by the year
        "Team Type", "Difference (A - B)",
        "Value", 
            CALCULATE(
                SUM('YourTable'[Value]),
                'YourTable'[Forecast Team Column] = "Forecast team A"
            )
            -
            CALCULATE(
                SUM('YourTable'[Value]),
                'YourTable'[Forecast Team Column] = "Forecast team B"
            )
    )
)

As I said, an approach together with your business team may make things easier. It's part of the data driven culture process.

---

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly. Appreciate your KUDO if you find it useful.