Filtered measure that ignores a slice

[oscarmqz]

Hi I have a matrix with a single measure which is a delta

Pseudo formula is: CurrentValues - ValuesSelected (From a cycle slicer)

For the CurrentValues part I want to ignore the cycle slicer with the below formula

mSavingsDelta = CALCULATE(SUM('CIP-ReportingDataNEW'[Savings (K$)]),'CIP-ReportingDataNEW'[Source]="Current",ALL('CIP-ReportingDataNEW'[Cycle]))

This formula works just fine inside a card even selecting something in the cycle slicer, but it doesn't work on a matrix...

I can't edit the interaction with the slicer because I still need the second part of the delta formula to react to the slicer which will simply by - sum ('CIP-ReportingDataNEW'[Savings (K$)])

Any help is appreciated!

[V-lianl-msft · Accepted solution]

Hi @oscarmqz ,

First create an unrelated Cycle table as slicer.

Then create a measure like this :

measure =
VAR mSavingsDelta =
    CALCULATE (
        SUM ( 'CIP-ReportingDataNEW'[Savings (K$)] ),
        'CIP-ReportingDataNEW'[Source] = "Current"
    )
VAR selectedvalue_ =
    CALCULATE (
        SUM ( 'CIP-ReportingDataNEW'[Savings (K$)] ),
        FILTER (
            'CIP-ReportingDataNEW',
            'CIP-ReportingDataNEW'[Cycle] = SELECTEDVALUE ( 'TABLE'[Cycle] )
        )
    )
RETURN
    mSavingsDelta - selectedvalue_

If the problem persists,could you share the sample pbix or sample data?

Please mask any sensitive data before uploading

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

[oscarmqz]

Thanks @V-lianl-msft this is what I ended up figuring out too. Appreciate your help guys!

[pranit828]

HI @oscarmqz 

try

mSavingsDelta = CALCULATE(SUM('CIP-ReportingDataNEW'[Savings (K$)]),'CIP-ReportingDataNEW'[Source]="Current",ALLEXCEPT('CIP-ReportingDataNEW'[Cycle]))

[oscarmqz]

Already tried that but using allexcept ignores all the attributes in the matrix showing the grand total for "current" in all the cells