topic Re: Custom Subtotals in Developer

Custom Subtotals

mestra25 — Fri, 25 Aug 2023 00:12:41 GMT

Hi Everyone,

I am working on a dashboard and I wanted to remove a column from the over all total, and add it back at the end. How can I accomplish this?

In this instance, I would want to remove "Unallocated Strategic Investments" from the Total, turn that total into a "Sub Total", and then add "Unallocaed Strategic Investments" back into the subtotal to create a new grand total.

Re: Custom Subtotals

Martin_D — Fri, 25 Aug 2023 03:03:58 GMT

Hi @mestra25 ,

You need to use the following DAX functions:

ISINSCOPE to determine whether you are calcualting the grand total
CALCULATE to exclude "Unallocated Strategic Investments" if you are in the grand total

Assuming the top level in your rows hierarchy is the column 'Table'[Entity] then this would be the new measure that you would create for the [CM Adj Bgt] measure (for all other measures accordingly):

CM Adj Bgt New Total = IF ( NOT ( ISINSCOPE ( 'Table'[Entity] ) ), CALCUALTE ( [CM Adj Bgt], KEEPFILTERS ( 'Table'[Entity] <> "Unallocated Strategic Investments" ) ), [CM Adj Bgt] )

Martin

Re: Custom Subtotals

mestra25 — Fri, 25 Aug 2023 16:13:19 GMT

@Martin_D Thanks for this. Now, is there a way to add "Unallocated Strategic Invesments to this subtotal and get a new grand total, like the below where the subtotal is the sum of all other entities and then I add back unallocated Strateigc investments?

Re: Custom Subtotals

Martin_D — Fri, 25 Aug 2023 20:10:52 GMT

@mestra25 you would solve more complex scenarios with a mapping table rather than writing more and more DAX code. The mapping table would assign which numbers go into which lines. Think like the numbers being assigned to general ledger accounts. Then the numbers in each line are calculated as a sum of different G/L accounts.

Line

Line ID	Line Label
1	Subtotal
2	Unallocated Stratgic Investment
3	Grand Total

Mapping (m:n)

Line ID	G/L Account No.
1	111111
1	222222
2	333333
3	111111
3	222222
3	333333

G/L Account

G/L Account No.	G/L Account Label
111111	Account A
222222	Account B
333333	Unallocated Stratgic Investment

G/L Entries

Date	G/L Account No.	Amount
2023-04-07	111111	300.00
2023-04-15	222222	5.95
2023-04-17	333333	8.00
2023-04-17	222222	150.00
2023-04-30	111111	50.00

Example

The relationsships are:

unidirectional one to many between Line and Mapping
bidirectional many to one between Mapping and G/L Account
unidirection ont to many between G/L Account and G/L Entries

Then the measure is just SUM('G/L Entries'[Amount]) everything else is done by the relationships.

Martin