Summarizing Pivot Rows to get Top 10 Overbooked by Budget Type

[mond007]

Summarizing Pivot Rows to get Top 10 Overbooked by Budget Type</SPAN></SPAN>

Hi I am struggling with find the top 10 Overbooked depts. My requirement seems to be easy. I need to find the Top 10 Overbooked where the “ACTUAL HEAD BOOKING + EQUIVALENCE > ASSIGNED BUDGET”</SPAN></SPAN>

The underlying data is vast i.e 70,000 rows plus. </SPAN></SPAN>

The data is simple. </SPAN></SPAN>
FY Report PT Dept Budget Type Programme PT Total Apr May Jun</SPAN></SPAN>
FY2011 PT-20 REQUIRED OPERATE Program 1 PT-2 2 2 2</SPAN></SPAN>
FY2011 PT-21 ACTUAL HEAD BOOKINGS Program 2 PT-2 1 1 1</SPAN></SPAN>
FY2011 PT-21 EQUIVALENCE Program 3 PT-2 2 2 2</SPAN></SPAN>
FY2012 PT-21 FORECASTED BUDGET Program 4 PT-2 3 3 3</SPAN></SPAN>
FY2012 PT-22 ACTUAL HEAD BOOKINGS Program 5 PT-2 1 1 1</SPAN></SPAN>
FY2012 PT-30 EQUIVALENCE Program 6 PT-3 1 1 1</SPAN></SPAN>
FY2012 PT-30 REQUIRED OPERATE Program 7 PT-3 1 1 1</SPAN></SPAN>

Row Labels Sum of Apr_Plus_May</SPAN></SPAN>
FY2011 1189.34387</SPAN></SPAN>
ACTUAL HEAD BOOKING </SPAN></SPAN>
PT-1 2.18108</SPAN></SPAN>
PT-2 186.76359</SPAN></SPAN>
EQUIVALENCE </SPAN></SPAN>
PT-2 101.310862</SPAN></SPAN>
ASSIGNED BUDGET </SPAN></SPAN>
PT-1 2</SPAN></SPAN>
PT-2 336.78</SPAN></SPAN>
FORECASTED HEADS 209.1500009</SPAN></SPAN>
REQUIRED OPERATE 351.1583375</SPAN></SPAN>
FY2012 1684.718573</SPAN></SPAN>
ACTUAL HEAD BOOKING </SPAN></SPAN>
PT-1 3.904052</SPAN></SPAN>
PT-2 341.524218</SPAN></SPAN>
EQUIVALENCE </SPAN></SPAN>
PT-1 0.404053</SPAN></SPAN>
PT-2 62.443266</SPAN></SPAN>
ASSIGNED BUDGET </SPAN></SPAN>
PT-1 21.376</SPAN></SPAN>
PT-2 411.0226</SPAN></SPAN>
FORECASTED HEADS 353.5506036</SPAN></SPAN>
REQUIRED OPERATE 490.4937808</SPAN></SPAN>
Grand Total 2874.062444

</SPAN></SPAN>Sorry the above doesn't show the PIVOT very well.

I have tried the Filter whereby I can ‘filter by’ the TOP 10 values but I cannot get data in the same rows of the pivot according to my formulae. There are several data slicers acting on the PIVOT table therefore dynamically changing the table applying the various Slicer filters. I need to keep the solution within the Pivot table so that the TOP 10 Overbooked (and Underbooked) change depending upon the Slicer Selections i.e. by Dept or Program etc..</SPAN></SPAN>

Essentially, I need to get ACTUAL HEAD BOOKING + EQUIVALENCE > ASSIGNED BUDGET on the same row. I have tried various Calculated field etc but with little luck.</SPAN>

The problem is that ACTUAL HEAD BOOKING comes out on one row. and the EQUIVALENCE comes out as a Total on the next row thus not allowing me to check > than.</SPAN>

Any assistance would be greatly appreciated. </SPAN></SPAN>
Thanks in advance. Kuldip.</SPAN></SPAN>

 

[scottsen]

Hi Kuldip,

I'm not SUPER clear on your problem, but I THINK this is what you want? If you click on the "parent" field, then bring up Field Settings, you can switch to "Show item labels in tabular form" on the Layout & Print tab.

For filtering by top 10, the Top 10 under Value Filters let you pick ANY column, so as long as you have a column you want to filter on... you should be good to go?

 

[mond007]

Hi Scott,
Thans exactly what I did.

I created and added two columns one for ACTUAL HEAD BOOKINGS + EQUIV and another for the ASSIGNED BUDGET to the pivot data. I also created a calculated field that was one greater than the other.

I got a good result. I then applied "Filter Values" to get the TOP and BOTTOM 10 values and as a bonus added some data bars. Wicked indeed.

Thanks again.