Forum Discussion
How to add row to matrix calculating difference between two values ?
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
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!
Super UserHello 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.