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

DPCCGF's avatar
DPCCGF
Helper IV
1 year ago

Rita, first thanks for your other solution.  Regarding sharing of the data, how do I do that?  Basically, I have the matrix below that can be drilled down on rows.  Trying to get the Total Line to perform the following:

 

Gross Margin - Operating Expenses + Other Income - Other Expense.  Right now by default it summarizes everything.

 

 

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

  • DPCCGF's avatar
    DPCCGF
    Helper IV
    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
      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!