Need PowerPivot Slicer based on Measure calculations

[powerpivotlegal]

Hello,

Is it possible to create a slicer with the options "Over Budget", "Under Budget" based on values calculated from a measure?

In the sample pivot table results below, "Remaining Budget" and "%Budget Used" are the measures. I want a slicer that returns all the Matter Numbers (e.g. "6828400-001002) by quarter that are Over/Under Budget.

I wanted something more user-friendly than just a KPI.
[TABLE="width: 535"]
<tbody>[TR]
[TD]Row Labels[/TD]
[TD]Fees Billed[/TD]
[TD]Unbilled Fees[/TD]
[TD]Budgets[/TD]
[TD]Remaining Budget[/TD]
[TD]%Budget Used[/TD]
[/TR]
[TR]
[TD]68284000-001002[/TD]
[TD="align: right"]$339,351.56[/TD]
[TD="align: right"]$22,731.27[/TD]
[TD="align: right"]$400,000.00[/TD]
[TD] $ 37,917.17[/TD]
[TD="align: right"]90.52%[/TD]
[/TR]
[TR]
[TD]FY14-Q1[/TD]
[TD="align: right"]$59,168.92[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$100,000.00[/TD]
[TD] $ 40,831.08[/TD]
[TD="align: right"]59.17%[/TD]
[/TR]
[TR]
[TD]FY14-Q2[/TD]
[TD="align: right"]$124,424.75[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$100,000.00[/TD]
[TD] $ (24,424.75)[/TD]
[TD="align: right"]124.42%[/TD]
[/TR]
[TR]
[TD]FY14-Q3[/TD]
[TD="align: right"]$155,757.89[/TD]
[TD="align: right"]$22,731.27[/TD]
[TD="align: right"]$100,000.00[/TD]
[TD] $ (78,489.16)[/TD]
[TD="align: right"]178.49%[/TD]
[/TR]
[TR]
[TD]FY14-Q4[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]$100,000.00[/TD]
[TD] $ 100,000.00[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]68284000-001003[/TD]
[TD="align: right"]$77,441.77[/TD]
[TD="align: right"]$922.50[/TD]
[TD="align: right"]$100,000.00[/TD]
[TD] $ 21,635.73[/TD]
[TD="align: right"]78.36%[/TD]
[/TR]
[TR]
[TD]FY14-Q1[/TD]
[TD="align: right"]$41,811.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$25,000.00[/TD]
[TD] $ (16,811.00)[/TD]
[TD="align: right"]167.24%[/TD]
[/TR]
[TR]
[TD]FY14-Q2[/TD]
[TD="align: right"]$16,479.73[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$25,000.00[/TD]
[TD] $ 8,520.27[/TD]
[TD="align: right"]65.92%[/TD]
[/TR]
[TR]
[TD]FY14-Q3[/TD]
[TD="align: right"]$19,151.04[/TD]
[TD="align: right"]$922.50[/TD]
[TD="align: right"]$25,000.00[/TD]
[TD] $ 4,926.46[/TD]
[TD="align: right"]80.29%[/TD]
[/TR]
[TR]
[TD]FY14-Q4[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]$25,000.00[/TD]
[TD] $ 25,000.00[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]68284000-001004[/TD]
[TD="align: right"]$79,337.47[/TD]
[TD="align: right"]$19,665.11[/TD]
[TD="align: right"]$100,000.00[/TD]
[TD] $ 997.42[/TD]
[TD="align: right"]99.00%[/TD]
[/TR]
[TR]
[TD]FY14-Q1[/TD]
[TD="align: right"]$23,081.37[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$25,000.00[/TD]
[TD] $ 1,918.63[/TD]
[TD="align: right"]92.33%[/TD]
[/TR]
[TR]
[TD]FY14-Q2[/TD]
[TD="align: right"]$50,663.50[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$25,000.00[/TD]
[TD] $ (25,663.50)[/TD]
[TD="align: right"]202.65%[/TD]
[/TR]
[TR]
[TD]FY14-Q3[/TD]
[TD="align: right"]$5,592.60[/TD]
[TD="align: right"]$19,665.11[/TD]
[TD="align: right"]$25,000.00[/TD]
[TD] $ (257.71)[/TD]
[TD="align: right"]101.03%[/TD]
[/TR]
[TR]
[TD]FY14-Q4[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]$25,000.00[/TD]
[TD] $ 25,000.00[/TD]
[TD] [/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col><col></colgroup>[/TABLE]


Any help would be greatly appreciated.

 

[scottsen]

This is doable, if a bit of a pain to explain. You will need to use a disconnected slicer (to use a powerpivotpro term). Google his site for some of the techniques there.

I think what I would do is have another measure that returns "1" only if a row is over budget AND the disconnected slicer is set to Over Budget. Then use "normal" excel techniques to filter a column with the 0/1 to only shows "1"s. You could then hide that column.

Make sense?

 

[powerpivotlegal]

Hi Scott,

Not sure I understand the second approach.

I created the measure OverBudget:=IF([%Budget Used]>=1,1,0) to give me the 0 and 1 values in a "Over Budget" column. I then created a table in PowerPivot with these options to create the disconnected slicer.

Not sure what you mean by filtering the column. The pivot table will not allow me to filter on the new "Over Budget" column. Plus, how do I link the disconnected slicer? The powerpivotpro posts talk about writing additional measures.

 

[scottsen]

Say the table supporting your disconnected "Over Budget" slicer looks like

Name Value
Over Budget 1
Under Budget 2

Let's call it OverUnderIndicator

So, you write a measure say...

ShouldInclude := COUNTROWS(OverUnderIndicator) = 2 ||
( MIN(OverUnderIndicator[Value]) = 1 && [%Budget Used] >= 1 ) ||
( MIN(OverUnderIndicator[Value]) = 2 && [%Budget Used] < 1 )

So, assuming you put OverUnderIndicator[Name] on a slicer, and Matter Numbers on Rows... you could add this measure on values.
It is going to return TRUE if
- you haven't sliced (COUNTROWS = 2), or
- if you sliced to OverBudget (min = 1) and you are over budget (% >= 1), or
- if you sliced to UnderBudget (min = 2) and you are under budget (% < 1)

So, at this point you have a measure flipping around between true and false, but no ACTUAL filtering happening. This is where I am saying "use normal excel filtering".

Just use a "Value Filter" (from the drop down menu on top left of Pivot), to only include rows where above measure is true.

 

[powerpivotlegal]

I now have a slicer captioned "Name" with Over Budget and Under Budget for slicer options. The measure populates the "ShouldInclude" column of the PivotTable with all TRUES when no slicer option is selected. If I select "Over Budget", the matters over budget show FALSE. I think this is all good so far.
[TABLE="width: 544"]
<tbody>[TR]
[TD]Matters[/TD]
[TD]Fees Billed[/TD]
[TD]Unbilled Fees[/TD]
[TD]Sum of Quarterly Budget[/TD]
[TD]%Budget Used[/TD]
[TD]ShouldInclude[/TD]
[/TR]
[TR]
[TD]68284000-001002[/TD]
[TD="align: right"]$339,351.56[/TD]
[TD="align: right"]$22,731.27[/TD]
[TD="align: right"]$400,000.00[/TD]
[TD="align: right"]90.52%[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]68284000-001003[/TD]
[TD="align: right"]$77,441.77[/TD]
[TD="align: right"]$922.50[/TD]
[TD="align: right"]$100,000.00[/TD]
[TD="align: right"]78.36%[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]68284000-001004[/TD]
[TD="align: right"]$79,337.47[/TD]
[TD="align: right"]$19,665.11[/TD]
[TD="align: right"]$100,000.00[/TD]
[TD="align: right"]99.00%[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]68284000-001005[/TD]
[TD="align: right"]$176,294.47[/TD]
[TD="align: right"]$325.50[/TD]
[TD="align: right"]$100,000.00[/TD]
[TD="align: right"]176.62%[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]68284000-001006[/TD]
[TD="align: right"]$20,238.43[/TD]
[TD="align: right"]$2,254.50[/TD]
[TD="align: right"]$100,000.00[/TD]
[TD="align: right"]22.49%[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]68284000-001007[/TD]
[TD="align: right"]$103,016.44[/TD]
[TD="align: right"]$8,528.33[/TD]
[TD="align: right"]$100,000.00[/TD]
[TD="align: right"]111.54%[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]68284000-001008[/TD]
[TD="align: right"]$47,577.43[/TD]
[TD="align: right"]$10,251.56[/TD]
[TD="align: right"]$100,000.00[/TD]
[TD="align: right"]57.83%[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]68284000-001009[/TD]
[TD="align: right"]$78,737.76[/TD]
[TD="align: right"]$637.00[/TD]
[TD="align: right"]$100,000.00[/TD]
[TD="align: right"]79.37%[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]68284000-001010[/TD]
[TD="align: right"]$62,752.89[/TD]
[TD="align: right"]$192,582.73[/TD]
[TD="align: right"]$100,000.00[/TD]
[TD="align: right"]255.34%[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]68284000-001011[/TD]
[TD="align: right"]$63,112.18[/TD]
[TD="align: right"]$10,694.01[/TD]
[TD="align: right"]$100,000.00[/TD]
[TD="align: right"]73.81%[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]68284000-001012[/TD]
[TD="align: right"]$46,588.91[/TD]
[TD="align: right"]$6,535.50[/TD]
[TD="align: right"]$100,000.00[/TD]
[TD="align: right"]53.12%[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col><col></colgroup>[/TABLE]


However, the Value Filter does not work when I try to enter:

Show Items for which "ShouldInclude" equals TRUE. The error message wants me to enter a number in some range. It does not matter if I set the format to text. If I try to set the filter to eqauls "1" (the value of Over Budget), all data in the pivot table disappears.

What am I missing?

 

[scottsen]

Ya know, I was just playing with something similiar, and ended up switching away from TRUE/FALSE, and going with 1/0, because the Value Filter was much happier with that. Give that a try.

 

[powerpivotlegal]

Not sure where I'm supposed to put 1/0. In the measure? If so, how? In the table, OverUnderIndicator? Sorry for all the questions, but I'm new to DAX formulas, especially COUNT or IF(Values) formulas. My background is legal not finance/analytics.

 

[scottsen]

No apology necessary. Most of my clients are the same, and it's what I love about Power Pivot. Power into EXACTLY the right hands

We had:
ShouldInclude := COUNTROWS(OverUnderIndicator) = 2 ||
( MIN(OverUnderIndicator[Value]) = 1 && [%Budget Used] >= 1 ) ||
( MIN(OverUnderIndicator[Value]) = 2 && [%Budget Used] < 1 )

Let's change that to:
ShouldInclude := IF(COUNTROWS(OverUnderIndicator) = 2 ||
( MIN(OverUnderIndicator[Value]) = 1 && [%Budget Used] >= 1 ) ||
( MIN(OverUnderIndicator[Value]) = 2 && [%Budget Used] < 1 ), 1, 0)

So that instead of returning TRUE/FALSE, that measure will return 1 or 0. That should also the Value Filter to work.