Forum Discussion

oscarmqz's avatar
oscarmqz
Icon for Advocate II rankAdvocate II
6 years ago
Solved

Filtered measure that ignores a slice

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's avatar
    V-lianl-msft
    6 years ago

    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.

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    HI oscarmqz 

    try

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

    • oscarmqz's avatar
      oscarmqz
      Icon for Advocate II rankAdvocate II
      6 years ago

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

       

  • V-lianl-msft's avatar
    V-lianl-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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.