Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Remove slicers involvement in a calculation

Hi guys,

 

I have two excel sheets I wan't to see the % diff between. 

Unfortunately they are both very different in the setup why I created some calculations.

 

However, for some reason my Quarter slicer changes my calculation to be smaller than it is. 

See picture:

 

https://imgur.com/D22ptZc

 

Can I make my slicer not affect Q1 but only AMOUNT?

 

Thanks,

 

PS: I can't create a Q1 calculation (summing Jan, Feb and March) from AMOUNT due to its format - I need the slicer.)

Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    6 years ago

    Hi Anonymous ,

     

    To work on it by using REMOVEFILTERS.

    Measure = CALCULATE(SUM('Table'[Duration (task hours)]),REMOVEFILTERS('date'[Date]))

     

     

4 Replies

  • Mariusz's avatar
    Mariusz
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi Anonymous 

     

    Most probably is one of the relationships that is affecting the calculation.

    You can use something like below considering you have a date dimension.

    Measure 8 = 
CALCULATE(
    SUM( table[Q1] ),
    ALL( date dimension table )
)

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.



    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Mariusz 

       

      Thanks! Doesn't seem to work tho as Q1 is a measurement. 

       

      I am more interested in if I can make my slicer only focus on óne measurement in a table? 

       

      Thanks!

      • Mariusz's avatar
        Mariusz
        Icon for Community Champion rankCommunity Champion
        6 years ago

        Hi Anonymous 

         

        Sure, however, your slicer column needs to be in a separate Table and your relationship inactive.

        Then you can invoke it like below by use of USERELATIONSHIP function.

        Value2 Afected = 
CALCULATE(
    SUM( 'Table'[Value2] ),
    USERELATIONSHIP( Slicer[Quarter], 'Table'[Quarter] )
)

         

        Best Regards,
        Mariusz

        If this post helps, then please consider Accepting it as the solution.

         

  • v-frfei-msft's avatar
    v-frfei-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

     

    To work on it by using REMOVEFILTERS.

    Measure = CALCULATE(SUM('Table'[Duration (task hours)]),REMOVEFILTERS('date'[Date]))