Reverse Total Calculation on Matrix Visual

[RENJITH_R_S]

Hello Friends, I have a matrix visual with Row Header as Title Contains 2 values benefits, cost and column header as year 2023,2024 and 2025 and value is a measure with calculation value = sum(column1)-sum(column2). See the screenshot. I need to update this measure as the total Net Total should be Benefits - Cost values. example consider Net total for year 2023, benefit value is 10 and cost value is 5, then Net Total = 10-5=5

please help on this

[RENJITH_R_S · Accepted solution]

I got answer

_Matrix_Variance$ =

if (ISINSCOPE('Table'[Title]),

[_Measure],

CALCULATE([_Measure],'Table'[Title]="Benefit")-CALCULATE([_Measure],'Table'[Title]="Cost"))

[burakkaragoz]

Hi @RENJITH_R_S ,

To achieve the desired Net Total calculation in your Power BI Matrix visual (where Net Total for each year is Benefit - Cost, and the grand total should also be the sum of [Benefit] - sum of [Cost]), you need to create a measure that calculates the Net Total dynamically at both row and total levels.

Please try the following DAX measure:

Net Total =
VAR Benefit = CALCULATE(SUM('Table'[Value]), 'Table'[Title] = "Benefit")
VAR Cost = CALCULATE(SUM('Table'[Value]), 'Table'[Title] = "Cost")
RETURN
Benefit - Cost

- Replace 'Table' and [Value] with your actual table and column names if they are different.

This measure will correctly show Net Total as Benefit minus Cost for each year, and the total at the bottom will be the sum of all benefits minus the sum of all costs, as you expect.

If you're using a matrix and your data structure is different, let me know more details or share a sample of your data model, and I can help adjust the formula.

Let me know if this solves your problem! If so, please accept this as the solution to help others as well.
translation and formatting supported by AI

[RENJITH_R_S]

@burakkaragoz Thanks for the input. But you are not clear on my requirement. I want to see the benefit, cost and Net total in a single measure

current measure = um(column1)-sum(column2)

so automatically Total with sum up the benefit value and cost value