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

     

     

     

     

Zanqueta's avatar
Zanqueta
Super User
9 months ago

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's avatar
    webe0436
    New Member
    9 months ago

    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's avatar
      Zanqueta
      Super User
      9 months ago

      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.