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 rem...
Need Help
Show and Tell
Tips and Tricks
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