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 Categor...
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. 

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.

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

    edhans 

     

    Thanks for the explanation.  You are right, the ALLEXCEPT() function is summarizing the values based on 'logdate' and 'name' when I apply the slicer.

    However, to give you some background on the my request.

     

    The idea is to get the 'Percentage Category' for a given range of 'logdate'.

    So, if choose last 2 months, then I want the category of employee productivity for that period.

     

    However, I have attached a sample of data in my pbix file.

    (Thanks for onedrive suggestion 😉)

    Sample pbix file 

     

    Regards,

    David

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

      ok. I think I can play with this. You need a date table though. I am leaving now but will try to play with this tonight at home a bit. I think I know what you are asking but someone may jump in first with this info and PBIX file.

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

        Well, I'm back to just having the visual you are using, a table, ignore interactions from the logdate slicer. For example, if I select Nov 15-Nov 17, I get three records = AAA/Workorder, BBB/non-workorder, and CCC/non-workorder. But if I add Nov 18 to the data, then I get an AAA/non-workorder added. Of course that will change the percentages. because there is an entirely new class of Name data, i.e. non-workorder for AAA.

         

        I'm not sure what you are expecting. So in my example above, show what you would want to show up if the logdate is Nov 15-Nov 17, then Nov 15-Nov 18.