Evaluation context challenge for sales variance % from first to last time selected

Anything related to PowerPivot and DAX Formuale
Eric Lacroix
Posts: 6
Joined: Mon Feb 01, 2016 11:37 pm

Evaluation context challenge for sales variance % from first to last time selected

Postby Eric Lacroix » Tue Feb 27, 2018 12:18 am

Hi all,

based on a slicer (weekid), I'm interested to calculate the % variance between the min and max (weekid) entry within this selection.
The following 3 measures calculate the % variance without any issue when there' no filter (no time dimension on the rows/columns):





The Variance % provide the result expected.
My requirement is to obtain the same Variance % even when there's a filter context from the rows (Weekid).
Here's a screenshot of my current table with the hardcoded expected result in the last column:

Evaluation Context table with Expected Result.jpg
Evaluation Context table with Expected Result.jpg (50.16 KiB) Viewed 498 times

I considered using an harvester measure, but I need the slicer to filter the data.
I also consider a calculated column but I need the range to be dynamic and couldn't create a calculate column capturing the slicer selection.

I believe I should be able to remove the filter from the row while maintaining the min and max from the slicer selection, but I can't see the solution.

Please find attached a pbix file with my current structure.
https://drive.google.com/open?id=1YNuCIs2-I922IzO1dDqmHK2Mev3poYtX

Thanks in advance

Eric

benholmes
Posts: 9
Joined: Tue Jan 30, 2018 9:22 pm

Re: Evaluation context challenge for sales variance % from first to last time selected

Postby benholmes » Tue Feb 27, 2018 10:34 pm

Hi Eric,

First up a bit of a caveat/apology in advance if I'm completely on the wrong path, as I'm only using Excel 2010 so can't have a look at your pbix file, nor can I use VAR to check my thoughts in PP. Having said that, I suspect that the problem comes about when you're setting the variables in your measures.



In this case, when you've got the week names on the rows of your report table, then VALUES ( 'Calendar'[Week Name] ) only has one entry, the week for the current row. So I think you need to do something like this to remove the restriction to the Week Name on the current row


I believe this should work, assuming you're always going to have all of the possible Week Names on the report. If you're going to be filtering/slicing then you may need to replace the ALL with ALLSELECTED to ensure you only capture the relevant Week Names. For example if you selected weeks 5-9 on the slicer, ALL would still return weeks 4 and 10 as the min and max, whereas ALLSELECTED would return 5 and 9. Again this is untested on your data/PBI but seems to work from a quick attempt to replicate your data in Excel 2010

Eric Lacroix
Posts: 6
Joined: Mon Feb 01, 2016 11:37 pm

Re: Evaluation context challenge for sales variance % from first to last time selected

Postby Eric Lacroix » Tue Feb 27, 2018 11:10 pm

Ben,

ALLSELECTED did the trick. I knew ALL wasn't working since it was removing all filters from the weekid, but ALLSELECTED was the solution I was looking for. Simple yet so useful.

The reviewed measure is the following:



Thanks for your assistance.

Eric


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 1 guest

cron