Banding in Excel

[JEB85]

Hi,</SPAN>

I’m trying to do some banding in Powerpivot.</SPAN>

I need to be able to band the ‘Days Open’ results – there’s one catch – there are duplicate ‘Order No.’s’ which will always have the same ‘Days Open’ number. I only want one record per dupe to be included within the bandings.</SPAN>

My ‘Date’ column is linked to a calendar.</SPAN>

I would like to be able to slice on the Include field ‘Y’ and slice on any particular dates within the calendar.</SPAN></SPAN>

[TABLE="width: 661"]
<TBODY>[TR]
[TD]DATA SET</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]DESIRED RESULT</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Date</SPAN>[/TD]
[TD]Order No.</SPAN>[/TD]
[TD]Days Open</SPAN>[/TD]
[TD]Include</SPAN>[/TD]
[TD][/TD]
[TD]Band Name</SPAN>[/TD]
[TD]No.</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]01/08/2013</SPAN>[/TD]
[TD="align: right"]1</SPAN>[/TD]
[TD="align: right"]4</SPAN>[/TD]
[TD]Y</SPAN>[/TD]
[TD][/TD]
[TD]0-5</SPAN>[/TD]
[TD="align: right"]2</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]01/08/2013</SPAN>[/TD]
[TD="align: right"]2</SPAN>[/TD]
[TD="align: right"]25</SPAN>[/TD]
[TD]Y</SPAN>[/TD]
[TD][/TD]
[TD]6-10</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]01/08/2013</SPAN>[/TD]
[TD="align: right"]3</SPAN>[/TD]
[TD="align: right"]38</SPAN>[/TD]
[TD]N</SPAN>[/TD]
[TD][/TD]
[TD]11-15</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]01/08/2013</SPAN>[/TD]
[TD="align: right"]1</SPAN>[/TD]
[TD="align: right"]4</SPAN>[/TD]
[TD]Y</SPAN>[/TD]
[TD][/TD]
[TD]16-20</SPAN>[/TD]
[TD="align: right"]1</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]02/08/2013</SPAN>[/TD]
[TD="align: right"]4</SPAN>[/TD]
[TD="align: right"]18</SPAN>[/TD]
[TD]Y</SPAN>[/TD]
[TD][/TD]
[TD]21-25</SPAN>[/TD]
[TD="align: right"]1</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]02/08/2013</SPAN>[/TD]
[TD="align: right"]5</SPAN>[/TD]
[TD="align: right"]62</SPAN>[/TD]
[TD]Y</SPAN>[/TD]
[TD][/TD]
[TD]26-30</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]02/08/2013</SPAN>[/TD]
[TD="align: right"]6</SPAN>[/TD]
[TD="align: right"]2</SPAN>[/TD]
[TD]Y</SPAN>[/TD]
[TD][/TD]
[TD]31-35</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]01/08/2013</SPAN>[/TD]
[TD="align: right"]2</SPAN>[/TD]
[TD="align: right"]25</SPAN>[/TD]
[TD]Y</SPAN>[/TD]
[TD][/TD]
[TD]36-40</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]41-45</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]46-50</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]50+</SPAN>[/TD]
[TD="align: right"]1</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL span=5><COL><COL></COLGROUP>[/TABLE]

Thanks

 

[miguel.escobar]

I'm pretty sure that you'd need to end up with the measure/calculated field approach as the calculated column wouldn't let you use the slicer correctly or it would be quite limited.
I think that I'd end up creating 2 measures one for when its filtered by something (Y or N) and the last one when's not filtered at all and use:
SUMMARIZE (to create a virtual table with a new column for the banding)
MAXX/MINX (in order to only get 1 row and don't use dupes)
and probably SUMX (To do the sum)
then create a new measure with CHOOSE that will select what measure to use using the slicer as a trigger

also, you might want to check your banding table as it probably needs more columns in order to be more specific. Check out this video as I think it might help you:
The Powerpivot Way - Total All LOOKUPs - YouTube

Hope this helps!

 

[JEB85]

Thanks, I could get rid of the 'y' and 'n' column if that would make the solution more straightforward?u

 

[miguel.escobar]

maybe there's a way to be more straightforward but I'd need more time to think about the solutions and I'm lacking time nowadays I'll try and see if during the week I could create a sample file with the solution for you but for now I'd recommend checking out that video so you can create the banding inside a measure and not a calculated column.

Best!