Matrix Summary Row Total

[DPCCGF]

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.  All "positive" numbers.  How can one easily get a total that takes Gross Margin - Operating Expenses+Other Income-Other Expense so I can have a NET.  Thanks!  

[DPCCGF · Accepted solution]

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]

Hi @DPCCGF 

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly 

[DPCCGF]

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]

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]

Here you go with the PBIX!  

https://1drv.ms/u/c/399d0072dbb282f0/EaS4AaxTQK9HtPU5VA9MnwABotNsUC-sUHyyMkON4QhQJw?e=bpA9Vc

[Ritaf1983 · Accepted solution]

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]

Hi Rita,

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

[Ritaf1983]

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

[DPCCGF · Accepted solution]

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.