Forum Discussion
Reverse Total Calculation on Matrix Visual
I got answer
_Matrix_Variance$ =if (ISINSCOPE('Table'[Title]),[_Measure],CALCULATE([_Measure],'Table'[Title]="Benefit")-CALCULATE([_Measure],'Table'[Title]="Cost"))
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
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