Different Drill down/drill up Subtotal Calculation in Matrix

[pk32]

I need Product to caluclate Avg of the months when they have values in them. As so, Handheld should read .186 when I drill up instead it sums up everything to .93(Aka sum). But I need my YearMonth to sum the expense which it is doing correctly. I've seen the hasonevalue/filter & ifinscope within the community, but never could get it to work.

[Anonymous · Accepted solution]

Hi @pk32 ,

You could use ISINSCOPE() or ISFILTERED() function.

create a new measure like below:

measure =
IF (
    ISINSCOPE ( [hierarchy] ),
    [value],
    SUMX ( ALLEXCEPT ( 'table', [hierarchy] ), [value] )
)

It will return sum of row values at subtotal.

Best Regards,

Jay