Forum Discussion
Helper VA problem with martix total
Islam , as mention at the begining, the reason for this os because in total row the difference is in minus and there is no result. The logic is as on detail row, if openings<expense, return 0.
So in order to have "sum" in totals, you need to use IfHasOneValue.
Attached file.
Example:
Regards,TotalPayback New =var _filterArea = FILTER('Table','Table'[Openings]>'Table'[Expenses])RETURNIF(HASONEVALUE('Table'[YearMonth]),IF([TotalOpenings]-[TotalExpenses] >0,[TotalOpenings]-[TotalExpenses]),SUMX(_filterArea,'Table'[Openings]-'Table'[Expenses]))
Nemanja Andic
Attached file.
Resident RockstarHi Islam ,
Total value in matrix doesn't behave as we used to see it in Excel.
Total value is not sum of all values in that column. On the other hand, total value is calculated for that "specific total row".
Example: if you are comparing if RequiredToPay is not blank then return some value, it will work on "detail row".
But for "total row" there is a value so it will return some value.
Most popular solution to this is to create condition to check if it is a detail row or total row.
Here we use IfHasOneValue or IsFiltereFunction.
More info you can find here: https://powerpivotpro.com/2014/01/grand-total-mania-totals-at-top-multiple-totals/
Regards,
Nemanja Andic
Helper V- nandic
Islam , could you please add additional information:
1) screenshot where we can also see results for measure "TotalExpenses"
2) dax for measures "TotalExpenses" and "TotalOpenings"
3) screenshot of model (relationships between tables)
All this is needed to find out where is the issue.
Thanks,
Nemanja