Forum Discussion

DPCCGF's avatar
DPCCGF
Helper IV
1 year ago
Solved

Matrix Summary Row Total

Hello, I have a Matrix with Column Headers of Months and Row Headers for Account Summary, Account Group and Account.  The Summary is Gross Margin, Operating Expenses, Other Income and Other Expense. ...
Need Help
Show and Tell
  • DPCCGF's avatar
    DPCCGF
    1 year ago

    Thank you Rita, let me check the video.  If it works great!  If not I will send out the pbix file.  Thank you for your solution on the other item as well. 

  • Ritaf1983's avatar
    Ritaf1983
    1 year ago

    Hi DPCCGF 
    You can create Dax formula like mine (Dividing by a million is only to reduce the cognitive load of dealing with too many digits; you can skip it if you'd like.)

    Bud_ =
    var
    budget_ = sum('Balances'[Budget])
    var
    Gross_ = CALCULATE(sum('Balances'[Budget]),FILTER('Balances','Balances'[Account Summary]="Gross Margin"))
    VAR
    Operating_ = CALCULATE(sum('Balances'[Budget]),FILTER('Balances','Balances'[Account Summary]="Operating Expenses"))
    Var
    Other_ex = Operating_ = CALCULATE(sum('Balances'[Budget]),FILTER('Balances','Balances'[Account Summary]="Other Expenses"))
    VAR
    Other_in=  CALCULATE(sum('Balances'[Budget]),FILTER('Balances','Balances'[Account Summary]="Other Income"))
    RETURN
    if(HASONEFILTER(Balances[Account Summary]),budget_,Gross_-Operating_-Other_ex+Other_in)/1000000
     
    Result :

    The sample logic should be applied to all the measures.
    The pbix is attached

    If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

Ritaf1983's avatar
Ritaf1983
Super User
1 year ago

Hi DPCCGF 
According to sharing the file, you can save it in some public cloud and share via a link.
Regarding different logic for the total, in general, you can use functions like `ISINSCOPE` and `HASONEFILTER` to define a different calculation logic for the total row than what you have at the item level.
There is a video guide to how it works:
https://www.youtube.com/watch?v=xAZY9FyLDPo

For more specific assistance I need to see the pbix

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

  • Ritaf1983's avatar
    Ritaf1983
    Super User
    1 year ago

    Hi DPCCGF 
    You can create Dax formula like mine (Dividing by a million is only to reduce the cognitive load of dealing with too many digits; you can skip it if you'd like.)

    Bud_ =
    var
    budget_ = sum('Balances'[Budget])
    var
    Gross_ = CALCULATE(sum('Balances'[Budget]),FILTER('Balances','Balances'[Account Summary]="Gross Margin"))
    VAR
    Operating_ = CALCULATE(sum('Balances'[Budget]),FILTER('Balances','Balances'[Account Summary]="Operating Expenses"))
    Var
    Other_ex = Operating_ = CALCULATE(sum('Balances'[Budget]),FILTER('Balances','Balances'[Account Summary]="Other Expenses"))
    VAR
    Other_in=  CALCULATE(sum('Balances'[Budget]),FILTER('Balances','Balances'[Account Summary]="Other Income"))
    RETURN
    if(HASONEFILTER(Balances[Account Summary]),budget_,Gross_-Operating_-Other_ex+Other_in)/1000000
     
    Result :

    The sample logic should be applied to all the measures.
    The pbix is attached

    If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

    • DPCCGF's avatar
      DPCCGF
      Helper IV
      1 year ago

      Hi Rita,

       

      Can you help me determine why - when using your formula - getting incorrect totals when I have oddball numbers?  Thanks!

       

       

      • Ritaf1983's avatar
        Ritaf1983
        Super User
        1 year ago

        Hi DPCCGF 
        The number in brackets is negative, the formula returns a correct result , - - = +:

         

        If there's a need to override the accounting calculation, you can wrap the variables in the ABS function, which returns the absolute value. However, based on how it looks, it seems that an incorrect result will be produced.

        https://learn.microsoft.com/en-us/dax/abs-function-dax

        If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly