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>

DATA SET</SPAN>					DESIRED RESULT</SPAN>
Date</SPAN>	Order No.</SPAN>	Days Open</SPAN>	Include</SPAN>		Band Name</SPAN>	No.</SPAN>
01/08/2013</SPAN>	1</SPAN>	4</SPAN>	Y</SPAN>		0-5</SPAN>	2</SPAN>
01/08/2013</SPAN>	2</SPAN>	25</SPAN>	Y</SPAN>		6-10</SPAN>	0</SPAN>
01/08/2013</SPAN>	3</SPAN>	38</SPAN>	N</SPAN>		11-15</SPAN>	0</SPAN>
01/08/2013</SPAN>	1</SPAN>	4</SPAN>	Y</SPAN>		16-20</SPAN>	1</SPAN>
02/08/2013</SPAN>	4</SPAN>	18</SPAN>	Y</SPAN>		21-25</SPAN>	1</SPAN>
02/08/2013</SPAN>	5</SPAN>	62</SPAN>	Y</SPAN>		26-30</SPAN>	0</SPAN>
02/08/2013</SPAN>	6</SPAN>	2</SPAN>	Y</SPAN>		31-35</SPAN>	0</SPAN>
01/08/2013</SPAN>	2</SPAN>	25</SPAN>	Y</SPAN>		36-40</SPAN>	0</SPAN>
					41-45</SPAN>	0</SPAN>
					46-50</SPAN>	0</SPAN>
					50+</SPAN>	1</SPAN>

<TBODY>
</TBODY><COLGROUP><COL span=5><COL><COL></COLGROUP>

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!