How can I filter a PowerPivot table based on a calculated measure?

[res.marcus]

Hi,

I'm trying to build a system that can localize large deviations in survey data. Right now I'm using PowerPivot to calculate all the "weekly answer levels" for the survey questions (as a percentage). Also I have created a four-week moving average which I compare the to the weekly answer levels, and if the deviations are above a centain percentage level I return the number 1, else I return a 0.

Now I want to be able to filter out all questions that do not have any large deviations. Can I somehow use a calculated Boolean-measure to do this? Or are there any other smart ways to accomplish what I´m trying to do?

Best,
Marcus

 

[NickyvV]

Marcus,

If I understand it correctly you have already calculated the large deviations:
and if the deviations are above a centain percentage level I return the number 1, else I return a 0.
So you could filter (on a slicer or in the PivotTable) on this value 1 or 0 to show only the 1's. You could also build an IF-function around the other measure to show only deviations above that certain percentage.
Please let us know if I'm missing something.

HTH

 

[res.marcus]

Marcus,

If I understand it correctly you have already calculated the large deviations:
and if the deviations are above a centain percentage level I return the number 1, else I return a 0.
So you could filter (on a slicer or in the PivotTable) on this value 1 or 0 to show only the 1's. You could also build an IF-function around the other measure to show only deviations above that certain percentage.
Please let us know if I'm missing something.

HTH

Hi NickyvV - thanks for your reply,

The deviations are calculated using measures in PowerPivot - and it seems you cannot filter based on custom measures (?).

I know one alternative would be to do more of the calculations in my SQL-query - but it would be nice to know a general way of applying custom filters on PowerPivot tables.

I'm not sure what you mean by "build an IF-function around the other measure" - could you give me an example (I'm kinda new to DAX)?

Best,
Marcus

 

[powerpivotpro]

Have you tried the FILTER() function?

FILTER() – When, Why, & How to Use It « PowerPivotPro

Or maybe the "iffer blanker" technique:

The Incredible “Iffer-Blanker-Filter” Measure « PowerPivotPro

 

[res.marcus]

Hi Rob - thank you for your reply!

I've looked at those guides, but I cannot seem to get the result I want.

Below is a is a description of how the PowerPivot looks right now and a description on how I want it to look.




How it looks:

		SomeCustomer1	SomeCustomer2	SomeCustomer3
		SomeCountry1	SomeCountry2	SomeCountry3
		SomeIndustry1	SomeIndustry2	SomeIndustry3
Date	Values	SomeQuestion1	SomeQuestion2	SomeQuestion3
201230	Sum of Percentage	99,99994326	83,03094145	53,22606436
201230	4 week moving average
201230	IsDeviating	0	0	0
201231	Sum of Percentage	99,99998408	73,84489647	53,24027352
201231	4 week moving average	99,99994326	83,03094145	53,22606436
201231	IsDeviating	0	0	0
201232	Sum of Percentage	99,99998324	82,12073478	48,06779504
201232	4 week moving average	99,99996367	78,43791896	53,23316894
201232	IsDeviating	0	0	0
201233	Sum of Percentage	99,99995187	84,68759472	55,83635779
201233	4 week moving average	99,99997019	79,66552423	51,51137764
201233	IsDeviating	0	0	0
201234	Sum of Percentage	99,99992014	82,72898164	36,44609389
201234	4 week moving average	99,99996561	80,92104185	52,59262268
201234	IsDeviating	0	0	1

<colgroup><col><col><col><col span="2"></colgroup><tbody>
</tbody>

How I want it to look:

		SomeCustomer3
		SomeCountry3
		SomeIndustry3
	Values	SomeQuestion3
201230	Sum of Percentage	53,22606436
201230	4 week moving average
201230	IsDeviating	0
201231	Sum of Percentage	53,24027352
201231	4 week moving average	53,22606436
201231	IsDeviating	0
201232	Sum of Percentage	48,06779504
201232	4 week moving average	53,23316894
201232	IsDeviating	0
201233	Sum of Percentage	55,83635779
201233	4 week moving average	51,51137764
201233	IsDeviating	0
201234	Sum of Percentage	36,44609389
201234	4 week moving average	52,59262268
201234	IsDeviating	1

<colgroup><col><col><col></colgroup><tbody>
</tbody>

I calculate the 4 week moving average with this formula:
=IF(countrows(VALUES(Query[Date]))=1, CALCULATE(AVERAGEX(VALUES(Query[Date]), [Sum of Percentage]), Query[Date] <= values(Query[Date])-1 && Query[Date] > VALUES(Query[Date])-5), BLANK())

And I check the deviation with this formula:
IF(AND(ABS([Sum of Percentage]/[4 week moving average]-1)>.3, [4 week moving average]<>""), 1, 0)


Thanks a lot for helping me out!

Best,
Marcus

 

[powerpivotpro]

I don't understand why you can't go to Column Labels, filter, Value Filters, and then set a filter on [Devation Measure] > 0. I do this sort of thing all the time so I may be missing something.

 

[NickyvV]

Maybe this [Deviation Measure] is not yet in the PT? You'll have to add it to the PT to be able to filter on it. You could hide the column afterwards if you want it not to be visible.

 

[powerpivotpro]

Actually with PowerPivot you CAN filter on measures even if they are not on the pivot. It rocks.

 

[res.marcus]

I don't understand why you can't go to Column Labels, filter, Value Filters, and then set a filter on [Devation Measure] > 0. I do this sort of thing all the time so I may be missing something.

Hi,

If I do, the table shows nothing - I'm guessing that the filters work with the grand totals (?).

Best,
Marcus

 

[res.marcus]

Hi again,

The best thing I've managed to produce so far is a PowerPivot-table only showing deviations. That is, it shows blank cells for Weeks that arent deviating.

Best,
Marcus