cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
vinaydavid Regular Visitor
Regular Visitor

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' %.

2.JPG

 

3.JPG7.JPG

 

5.JPG4.JPG

 

Thanks for your support in advance!

 

Regards,

vinaydavid

1 ACCEPTED SOLUTION

Accepted Solutions
Super User II
Super User II

Re: Slicer/Filter should not change the Measure calculation

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. 


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!


MCSA: BI Reporting

View solution in original post

10 REPLIES 10
Super User II
Super User II

Re: Slicer/Filter should not change the Measure calculation

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.


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!


MCSA: BI Reporting
vinaydavid Regular Visitor
Regular Visitor

Re: Slicer/Filter should not change the Measure calculation

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.

 

 

Super User II
Super User II

Re: Slicer/Filter should not change the Measure calculation


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.


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!


MCSA: BI Reporting
vinaydavid Regular Visitor
Regular Visitor

Re: Slicer/Filter should not change the Measure calculation

@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

Super User II
Super User II

Re: Slicer/Filter should not change the Measure calculation

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.


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!


MCSA: BI Reporting
Super User II
Super User II

Re: Slicer/Filter should not change the Measure calculation

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.

 


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!


MCSA: BI Reporting
vinaydavid Regular Visitor
Regular Visitor

Re: Slicer/Filter should not change the Measure calculation

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

 

1.png

Thanks,

David

 

Super User II
Super User II

Re: Slicer/Filter should not change the Measure calculation

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. 


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!


MCSA: BI Reporting

View solution in original post

vinaydavid Regular Visitor
Regular Visitor

Re: Slicer/Filter should not change the Measure calculation

@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 

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors