Forum Discussion

mestra25's avatar
mestra25
Frequent Visitor
2 years ago

Custom Subtotals

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. 

Need Help
Show and Tell
Tips and Tricks

3 Replies

  • Martin_D's avatar
    Martin_D
    Solution Sage
    2 years ago

    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]
)

     

    BR

    Martin

  • mestra25's avatar
    mestra25
    Frequent Visitor
    2 years ago

    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?

     

     

    • Martin_D's avatar
      Martin_D
      Solution Sage
      2 years ago

      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 IDLine Label
      1Subtotal
      2Unallocated Stratgic Investment
      3Grand Total

       

      Mapping (m:n)

      Line IDG/L Account No.
      1111111
      1222222
      2333333
      3111111
      3222222
      3333333

       

      G/L Account

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

       

      G/L Entries

      DateG/L Account No.Amount
      2023-04-07111111300.00
      2023-04-152222225.95
      2023-04-173333338.00
      2023-04-17222222150.00
      2023-04-3011111150.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