Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Multiple totals on matrix

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 ...
Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    7 years ago

    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

v-lili6-msft's avatar
v-lili6-msft
Community Support
7 years ago

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's avatar
    Anonymous
    Not applicable
    7 years ago

    Lin,

     

    It works,

    Thank you

    Ed Dror

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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] = 8)
    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