PowerPivot Groups Hierarchy levels vs. Drilldown?

[shane_aldrich]

Hi All...

Is this even possible??? I'd like to have a slicer that displays hierarchy levels by grouping. In the example, the top is a current pivot layout (normal)...on the bottom is the desired layout.

I'd love to somehow have a slicer that represents each level...so if I click "Director" only 3 director rows display...if I click "Manager" only the 6 mangers would display...etc.

Pivot Table Results

Level actuals targets attainment
Director 1 2090 2523 82.8%
Manager 1 1020 1192 85.6%
D2D Agent 1 200 249 80.3%
D2D Agent 2 202 255 79.2%
D2D Agent 3 204 244 83.6%
D2D Agent 4 206 271 76.0%
D2D Agent 5 208 261 79.7%
Manager 2 1070 1144 93.5%
D2D Agent 10 218 245 89.0%
D2D Agent 6 210 256 82.0%
D2D Agent 7 212 226 93.8%
D2D Agent 8 214 250 85.6%
D2D Agent 9 216 258 83.7%
Director 2 2290 2211 103.6%
Manager 3 1120 1164 96.2%
D2D Agent 11 220 286 76.9%
D2D Agent 12 222 260 85.4%
D2D Agent 13 224 236 94.9%
D2D Agent 14 226 255 88.6%
D2D Agent 15 228 204 111.8%
Manager 4 1170 1137 102.9%
D2D Agent 16 230 227 101.3%
D2D Agent 17 232 274 84.7%
D2D Agent 18 234 253 92.5%
D2D Agent 19 236 228 103.5%
D2D Agent 20 238 287 82.9%
Director 3 2490 2053 121.3%
Manager 5 1220 1143 106.7%
D2D Agent 21 240 280 85.7%
D2D Agent 22 242 213 113.6%
D2D Agent 23 244 239 102.1%
D2D Agent 24 246 257 95.7%
D2D Agent 25 248 234 106.0%
Manager 6 1270 1091 116.4%
D2D Agent 26 250 272 91.9%
D2D Agent 27 252 251 100.4%
D2D Agent 28 254 235 108.1%
D2D Agent 29 256 225 113.8%
D2D Agent 30 258 269 95.9%
Grand Total 6870 6787 101.2%


Desired Layout

Row Labels actuals targets attainment
Grand Total 6870 6787 101.2%
Director 1 2090 2523 82.8%
Director 2 2290 2211 103.6%
Director 3 2490 2053 121.3%
Manager 1 1020 1192 85.6%
Manager 2 1070 1144 93.5%
Manager 3 1120 1164 96.2%
Manager 4 1170 1137 102.9%
Manager 5 1220 1143 106.7%
Manager 6 1270 1091 116.4%
D2D Agent 1 200 249 80.3%
D2D Agent 2 202 255 79.2%
D2D Agent 3 204 244 83.6%
D2D Agent 4 206 271 76.0%
D2D Agent 5 208 261 79.7%
D2D Agent 6 210 256 82.0%
D2D Agent 7 212 226 93.8%
D2D Agent 8 214 250 85.6%
D2D Agent 9 216 258 83.7%
D2D Agent 10 218 245 89.0%
D2D Agent 11 220 286 76.9%
D2D Agent 12 222 260 85.4%
D2D Agent 13 224 236 94.9%
D2D Agent 14 226 255 88.6%
D2D Agent 15 228 204 111.8%
D2D Agent 16 230 227 101.3%
D2D Agent 17 232 274 84.7%
D2D Agent 18 234 253 92.5%
D2D Agent 19 236 228 103.5%
D2D Agent 20 238 287 82.9%
D2D Agent 21 240 280 85.7%
D2D Agent 22 242 213 113.6%
D2D Agent 23 244 239 102.1%
D2D Agent 24 246 257 95.7%
D2D Agent 25 248 234 106.0%
D2D Agent 26 250 272 91.9%
D2D Agent 27 252 251 100.4%
D2D Agent 28 254 235 108.1%

Thanks,

SHane

 

[scottsen]

Pretty sure I am not totally getting your question, but let's start with the easy part.

Add a column which is the "Level" ... and the only possible values are { Manager, Director, and D2D Agent }. That way you can slice on that...

 

[shane_aldrich]

Hi Scott...thanks for replying....

I have a small workbook that I'm doing my "testing" in. - I added a level table, created the relationship, and the functionality of the slicer it works fine...but my filtering isn't working properly when I filter the levels.

The actuals always sums that total of all actuals in the table, and targets only seems to work when the director level is filtered...

It may make more sends if I could attach the book or email it to you if you are open to that.

Shane

 

[scottsen]

sure, you can email a workbook... or drop a link here to dropbox/onedrive/googledrive and I'm happy to take a look.