Forum Discussion
Multiple totals on matrix
hi, Anonymous
I'm afraid that add an additional total row is not available for now.
And you may try this way to add a new custom row not a "total" row:
Step1:
Use this formula to create a new category dim table.
Dim CatName = UNION(SUMMARIZE(Account,Account[Category],Account[CatName]),ROW("Category",99,"CatName","Total Cost"))Step2:
Create a relationship with Account table
Step3:
Adjust your formula as below:
Original = VAR Billing = Calculate( Sum('Account'[Amount]) ,Account[Category] = 0) VAR Categories = Calculate( Sum(Account[Amount]) ,Account[Category] <> 0) Return IF(SELECTEDVALUE('Dim CatName'[CatName])="Total Cost",CALCULATE(SUM(Account[Amount]),FILTER(ALLSELECTED(Account),Account[Category] <> 0)), ABS(Billing - Categories))Result:
By the way, you could use sort by column to sort the column order
https://docs.microsoft.com/en-us/power-bi/desktop-sort-by-column
here is pbix file, please try it.
Best Regards,
Lin
hi, Anonymous
I'm afraid that add an additional total row is not available for now.
And you may try this way to add a new custom row not a "total" row:
Step1:
Use this formula to create a new category dim table.
Dim CatName = UNION(SUMMARIZE(Account,Account[Category],Account[CatName]),ROW("Category",99,"CatName","Total Cost"))
Step2:
Create a relationship with Account table
Step3:
Adjust your formula as below:
Original =
VAR Billing = Calculate(
Sum('Account'[Amount])
,Account[Category] = 0)
VAR Categories = Calculate(
Sum(Account[Amount])
,Account[Category] <> 0)
Return
IF(SELECTEDVALUE('Dim CatName'[CatName])="Total Cost",CALCULATE(SUM(Account[Amount]),FILTER(ALLSELECTED(Account),Account[Category] <> 0)),
ABS(Billing - Categories))
Result:
By the way, you could use sort by column to sort the column order
https://docs.microsoft.com/en-us/power-bi/desktop-sort-by-column
here is pbix file, please try it.
Best Regards,
Lin
Lin,
After testing it with one category as negative amount the expected result don't match
I added another toal cost to the Dim
And then I did
But is not working (I change Equipment and vendor Equipment to negative amount)
Thanks,