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