Forum Discussion

vinaydavid's avatar
vinaydavid
Icon for Helper III rankHelper III
6 years ago
Solved

Slicer/Filter should not change the Measure calculation

Hi Experts,

 

Is there a way, where we can exclude the impact of Slicer/Filter on a Measure calculation?

 

Here is the situation,

The below table calculation give me the right Percentage Category, however when I choose a value fromt he slicer, the categories change resulting in 'incorrect' %.

 

 

 

Thanks for your support in advance!

 

Regards,

vinaydavid

Need Help
  • edhans's avatar
    edhans
    6 years ago

    Just a clarification: The table would maintain interaction. I was suggesting selecting the slicer, going to interaction, and disabling the table only. All other visuals and slicers would still work on the table. Just turning it off for the date slicer. 

     

    Does the following work for you?

    New Percentage = 
VAR Numerator =
    CALCULATE(
        SUM('Time Tracker Data'[Hours]),
        ALLEXCEPT(
            'Time Tracker Data',
            'Time Tracker Data'[Name],
            'Time Tracker Data'[LogDate],
            'Time Tracker Data'[Productive/ Non-productive]
        )
    )
VAR Denominator =
    CALCULATE(
        SUM('Time Tracker Data'[Hours]),
        REMOVEFILTERS('Time Tracker Data'[Name],'Time Tracker Data'[LogDate])
    )
RETURN
DIVIDE(Numerator,Denominator,0)

     

    Here, the denominator ignores the context filters on the Name field and the log date field. 

    If that isn't what you want, you can change what fields are in the REMOVEFILTERS() function. At this point it is really figuring out which filters you want to keep, and those filters are povided by the table rows Name and Productive/Non-Productive (controlled by a slicer) and the log date. 

    I'm not sure I've helped too much here. I tend to do the math in Excel or on a calculator to know what results I want then work on the filters to make sure I get the right results, then make sure the audience will understand it without me having to explain for 15min what the calculation is doing. 

10 Replies

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

    Yes. You'll need to use ALL, ALLEXCEPT, or some other way to remove the filter that the slicer is applying, within the measure that is calculating the results. You didn't give enough details on the measure though.

     

    You could select the slicer, then the Format bar in the desktop, and Edit Interactions, then tell it not to interact with another visual, but that will turn off ALL impacts of the slicer on that visual, not just the measure.

     

    If you want more help, please link to a PBIX file that has dummy data or data that you don't care about to make it easier on whomever can assist. I can help, but someone may jump on it first. Don't want to waste time keying in data and creating a model if you have already done that.

    • vinaydavid's avatar
      vinaydavid
      Icon for Helper III rankHelper III
      6 years ago

      Thanks edhans 

       

      I have been trying to upload the sample PBIX files (from googledrive etc.,) but dint find the option to attach files and also the url option says the link is broken r not complete. No idea.

       

      So here are the details requested....

      The code used for the measure.

       

      Percentage = 
VAR __NUM =
    CALCULATE (
        SUM ( 'Time Tracker Data'[Hours] ),
        ALLEXCEPT (
            'Time Tracker Data',
            'Time Tracker Data'[Name],
            'Time Tracker Data'[LogDate],
            'Time Tracker Data'[Productive/ Non-productive]
        )
    )
VAR __DEN =
    CALCULATE (
        SUM ( 'Time Tracker Data'[Hours] ),
        ALLEXCEPT (
            'Time Tracker Data',
            'Time Tracker Data'[Name],
            'Time Tracker Data'[LogDate]
        )
    )
RETURN DIVIDE ( __NUM, __DEN, 0 )

      Also to mention, I have a slicer on Logdate as well.

       

      Observation: When the slicer on logdate is removed, the percentages values are correct even when we choose either Work Order or Non-Work order.

      But when I place the 'Logdate' Slicer, and apply some date selection (along with selection on work order/non-work order), then the percentages are changing.

       

       

      • edhans's avatar
        edhans
        Icon for Community Champion rankCommunity Champion
        6 years ago
        Observation: When the slicer on logdate is removed, the percentages values are correct even when we choose either Work Order or Non-Work order.

        But when I place the 'Logdate' Slicer, and apply some date selection (along with selection on work order/non-work order), then the percentages are changing.

         

         

        Your ALLEXCEPT() functions both list LogDate, so that filter from the slicer is being honored, not removed. Remove those rows in your functions (or comment them out) and see if that is what you want.

         

        I find OneDrive to be an excellent way to share PBIX files. Do it all of the time. Just uncheck the "allow editing" box when you do it.