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