# 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

NickyvV said:


> 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:*


  SomeCustomer1SomeCustomer2SomeCustomer3  SomeCountry1SomeCountry2SomeCountry3  SomeIndustry1SomeIndustry2SomeIndustry3DateValuesSomeQuestion1SomeQuestion2SomeQuestion3201230Sum of Percentage99,9999432683,0309414553,226064362012304 week moving average201230IsDeviating000201231Sum of Percentage99,9999840873,8448964753,240273522012314 week moving average99,9999432683,0309414553,22606436201231IsDeviating000201232Sum of Percentage99,9999832482,1207347848,067795042012324 week moving average99,9999636778,4379189653,23316894201232IsDeviating000201233Sum of Percentage99,9999518784,6875947255,836357792012334 week moving average99,9999701979,6655242351,51137764201233IsDeviating000201234Sum of Percentage99,9999201482,7289816436,446093892012344 week moving average99,9999656180,9210418552,59262268201234IsDeviating001

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

</tbody>


*How I want it to look:*


 SomeCustomer3 SomeCountry3 SomeIndustry3ValuesSomeQuestion3201230Sum of Percentage53,226064362012304 week moving average201230IsDeviating0201231Sum of Percentage53,240273522012314 week moving average53,22606436201231IsDeviating0201232Sum of Percentage48,067795042012324 week moving average53,23316894201232IsDeviating0201233Sum of Percentage55,836357792012334 week moving average51,51137764201233IsDeviating0201234Sum of Percentage36,446093892012344 week moving average52,59262268201234IsDeviating1

<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

powerpivotpro said:


> 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


----------



## 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


----------



## powerpivotpro

Can you send me the workbook, or a workbook with sample data illustrating the problem?


----------



## res.marcus

Hi,

I sent you a workbook with some sample data.

Best,
Marcus


----------

