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

R

res.marcus

New Member
Joined
Oct 18, 2012
Messages
19
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
 
Sort by date Sort by votes
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
 
Upvote 0
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
Click to expand...

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
 
Upvote 0
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:

[TABLE="width: 689"]
<colgroup><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD] [/TD]
[TD] [/TD]
[TD]SomeCustomer1[/TD]
[TD]SomeCustomer2[/TD]
[TD]SomeCustomer3[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD]SomeCountry1[/TD]
[TD]SomeCountry2[/TD]
[TD]SomeCountry3[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD]SomeIndustry1[/TD]
[TD]SomeIndustry2[/TD]
[TD]SomeIndustry3[/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]Values[/TD]
[TD]SomeQuestion1[/TD]
[TD]SomeQuestion2[/TD]
[TD]SomeQuestion3[/TD]
[/TR]
[TR]
[TD]201230[/TD]
[TD]Sum of Percentage[/TD]
[TD="align: right"]99,99994326[/TD]
[TD="align: right"]83,03094145[/TD]
[TD="align: right"]53,22606436[/TD]
[/TR]
[TR]
[TD]201230[/TD]
[TD]4 week moving average[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]201230[/TD]
[TD]IsDeviating[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]201231[/TD]
[TD]Sum of Percentage[/TD]
[TD="align: right"]99,99998408[/TD]
[TD="align: right"]73,84489647[/TD]
[TD="align: right"]53,24027352[/TD]
[/TR]
[TR]
[TD]201231[/TD]
[TD]4 week moving average[/TD]
[TD="align: right"]99,99994326[/TD]
[TD="align: right"]83,03094145[/TD]
[TD="align: right"]53,22606436[/TD]
[/TR]
[TR]
[TD]201231[/TD]
[TD]IsDeviating[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]201232[/TD]
[TD]Sum of Percentage[/TD]
[TD="align: right"]99,99998324[/TD]
[TD="align: right"]82,12073478[/TD]
[TD="align: right"]48,06779504[/TD]
[/TR]
[TR]
[TD]201232[/TD]
[TD]4 week moving average[/TD]
[TD="align: right"]99,99996367[/TD]
[TD="align: right"]78,43791896[/TD]
[TD="align: right"]53,23316894[/TD]
[/TR]
[TR]
[TD]201232[/TD]
[TD]IsDeviating[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]201233[/TD]
[TD]Sum of Percentage[/TD]
[TD="align: right"]99,99995187[/TD]
[TD="align: right"]84,68759472[/TD]
[TD="align: right"]55,83635779[/TD]
[/TR]
[TR]
[TD]201233[/TD]
[TD]4 week moving average[/TD]
[TD="align: right"]99,99997019[/TD]
[TD="align: right"]79,66552423[/TD]
[TD="align: right"]51,51137764[/TD]
[/TR]
[TR]
[TD]201233[/TD]
[TD]IsDeviating[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]201234[/TD]
[TD]Sum of Percentage[/TD]
[TD="align: right"]99,99992014[/TD]
[TD="align: right"]82,72898164[/TD]
[TD="align: right"]36,44609389[/TD]
[/TR]
[TR]
[TD]201234[/TD]
[TD]4 week moving average[/TD]
[TD="align: right"]99,99996561[/TD]
[TD="align: right"]80,92104185[/TD]
[TD="align: right"]52,59262268[/TD]
[/TR]
[TR]
[TD]201234[/TD]
[TD]IsDeviating[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]



How I want it to look:

[TABLE="width: 389"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD] [/TD]
[TD]SomeCustomer3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] [/TD]
[TD]SomeCountry3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] [/TD]
[TD]SomeIndustry3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Values[/TD]
[TD]SomeQuestion3[/TD]
[/TR]
[TR]
[TD]201230[/TD]
[TD]Sum of Percentage[/TD]
[TD="align: right"]53,22606436[/TD]
[/TR]
[TR]
[TD]201230[/TD]
[TD]4 week moving average[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]201230[/TD]
[TD]IsDeviating[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]201231[/TD]
[TD]Sum of Percentage[/TD]
[TD="align: right"]53,24027352[/TD]
[/TR]
[TR]
[TD]201231[/TD]
[TD]4 week moving average[/TD]
[TD="align: right"]53,22606436[/TD]
[/TR]
[TR]
[TD]201231[/TD]
[TD]IsDeviating[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]201232[/TD]
[TD]Sum of Percentage[/TD]
[TD="align: right"]48,06779504[/TD]
[/TR]
[TR]
[TD]201232[/TD]
[TD]4 week moving average[/TD]
[TD="align: right"]53,23316894[/TD]
[/TR]
[TR]
[TD]201232[/TD]
[TD]IsDeviating[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]201233[/TD]
[TD]Sum of Percentage[/TD]
[TD="align: right"]55,83635779[/TD]
[/TR]
[TR]
[TD]201233[/TD]
[TD]4 week moving average[/TD]
[TD="align: right"]51,51137764[/TD]
[/TR]
[TR]
[TD]201233[/TD]
[TD]IsDeviating[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]201234[/TD]
[TD]Sum of Percentage[/TD]
[TD="align: right"]36,44609389[/TD]
[/TR]
[TR]
[TD]201234[/TD]
[TD]4 week moving average[/TD]
[TD="align: right"]52,59262268[/TD]
[/TR]
[TR]
[TD]201234[/TD]
[TD]IsDeviating[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]


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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
:eeek:
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.
Click to expand...

Hi,

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

Best,
Marcus
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Solved
DAX measure that filters a table to a single row and returns text from a specified column (Excel365)
Replies
3
Views
573
Insert_Key
I
P
  • Question Question
PowePivot - Create a slicer of a measure or calculate values from one table using filter from another table
Replies
5
Views
796
RoryA
RoryA
K
  • Solved
DAX median in pivot table for an "almost empty" column
Replies
1
Views
269
Katchi
K
jlhoover3
  • Solved
Power Query - Filter table column based on cell value
Replies
3
Views
3K
alansidman
alansidman
S
  • Question Question
How to filter a measure
Replies
3
Views
2K
Peter_SSs
Peter_SSs

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top