Multiple totals on matrix

[Anonymous]

Hi there,

Please take a look at the PBIX and this photo and see if you can help me

I have one table name account and I seperate it into two tables but the requirement was if I can merge these two

Tables into one (see bottom page the excel version) - Can I have two seperate totals on same column in one matrix?

I solve it with t-SQL 4 itterations one for billing one for cost one for total cost and one for billing - ToalCost and it worls but I would like to do it in DAX

Thanks,

Ed Dror

[v-lili6-msft · Accepted solution]

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

[Anonymous]

Lin,

After testing it with one category as negative amount the expected result don't match 

I added another toal cost to the Dim

Dim CatName = UNION(SUMMARIZE(Account,Account[Category],Account[CatName]),ROW("Category",99,"CatName","Total Cost"),ROW("Category",999,"CatName","Cost Margin"))

And then I did

Original =

VAR Billing = Calculate(Sum('Account'[Amount]),Account[Category] = 0)

VAR Labor = Calculate(Sum(Account[Amount]),Account[Category] = 1)

VAR QM = Calculate(Sum(Account[Amount]),Account[Category] = 3)

VAR Misc = Calculate(Sum(Account[Amount]),Account[Category] = 4)

VAR Sub = Calculate(Sum(Account[Amount]),Account[Category] = 5)

VAR Equip = Calculate(Sum(Account[Amount]),Account[Category] = 6)

VAR Vendor = Calculate(Sum(Account[Amount]),Account[Category] = 7)

VAR Other = Calculate(Sum(Account[Amount]),Account[Category] = 😎

Var TC = Labor + QM + Misc + Sub + Equip + Vendor + Other

Return

IF(SELECTEDVALUE('Dim CatName'[CatName])="Total Cost",CALCULATE(SUM(Account[Amount]),FILTER(ALLSELECTED(Account),Account[Category] <> 0)),

IF(SELECTEDVALUE('Dim CatName'[CatName])="Cost Margin",CALCULATE(SUM(Account[Amount]),FILTER(ALLSELECTED(Account),Account[Category] <> 0)),

(Billing - TC)))
But is not working (I change Equipment and vendor Equipment to negative amount)
Thanks,

Ed Dror

[Anonymous]

Lin,

It works,

Thank you

Ed Dror

[Anonymous]

One more think

I got the total for table A by doing this

Original =

VAR Billing = Calculate(

Sum('Account'[Amount])

,Account[Category] = 0)

VAR Categories = Calculate(

Sum(Account[Amount])

,Account[Category] <> 0)

Return ABS(Billing - Categories)

Thanks,

Ed Dror