Filtering only the top level of a visual

[Anonymous]

Hello, 

I have a visual that i have drilling down by client and product type. I want the top level client visual to show only a certain threshold (i.e. >400 and <0 for big positiives and negatives), but when i drill down i want to show all values. 

I've tried creating a filter measure to exclude the 2nd level of the hierarchy (product type), but it isnt doing the trick. Heres the calc im using for that:

CALCULATE( [Var to plan], ALLEXCEPT('Communications Revenue','Communications Revenue'[Product type]))

Anyone have any ideas? Greatly appreciated. Thanks!

[v-yingjl · Accepted solution]

Hi @Anonymous ,

Based on your descrition, you can try to create a measure like this:

Measure =
IF (
    ISINSCOPE ( 'Table'[Product type] ),
    SUM ( 'Table'[Value] ),
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            ALL ( 'Table' ),
             ( 'Table'[Value] < 0
                || 'Table'[Value] > 400 )
                && 'Table'[Client] IN DISTINCT ( 'Table'[Client] )
        )
    )
)

Attached a sample file in the below, hopes to help you.

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.