Slicer/Filter should not change the Measure calculation

[vinaydavid]

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

[edhans]

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]

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]

---

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]

@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]

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]

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.

[vinaydavid]

@edhans 

Thanks for coming back.

Alright, here is what I wanted the visuals to show.

The 'Logdate' slicer is fixed say Nov 15 - Nov 18. (Real time - we might go with 'Last' 1 Months (mostly))

And the table should have interaction enabled (to show work order/Non work order).

Below is the situation which will effect the 'percentage category' based on the bucketed values.

I know, its because ALLEXCEPT based on logdate is causing this issue.

Is there a way, where we can handle the denominator value in percentage calculation to be fixed even after using the slicer (workorder/non workorder)?

Below picture shows the situation...

Thanks,

David

[edhans · Accepted solution]

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]

@edhans 

Just back from the Holidays,

Thanks for your time and also for the new functions.

I have done few changes in in the CALCULATE function to add, both (Numerator & Denominator)

'Time Tracker Data'[LogDate].Month,
'Time Tracker Data'[LogDate].Year,

And changed the Slicer to slice on Month and Year.

This helped to have right values even when we change the Filters or Time Slicers.

Regards,

David 

[edhans]

Great! Glad you were able to adapt my solution to your needs.