Forum Discussion
Custom Subtotals
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?
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.
BR
Martin
