Grouping by Numbers

[robtop]

Afternoon,

I'm pretty new to power piviot and am trying to figure out how to group by numbers to replicate something i had in an old pivot table report.

I want to group a sum insured column which could range anywhere from £0 to £50,000 by the following groups:

[TABLE="width: 148"]
<tbody>[TR]
[TD]0-2500[/TD]
[/TR]
[TR]
[TD]2500-5000[/TD]
[/TR]
[TR]
[TD]5000-7500[/TD]
[/TR]
[TR]
[TD]7500-10000[/TD]
[/TR]
[TR]
[TD]10000-12500[/TD]
[/TR]
[TR]
[TD]12500-15000[/TD]
[/TR]
[TR]
[TD]15000-17500[/TD]
[/TR]
[TR]
[TD]17500-20000[/TD]
[/TR]
[TR]
[TD]20000-22500[/TD]
[/TR]
[TR]
[TD]22500-25000[/TD]
[/TR]
[TR]
[TD]25000-27500[/TD]
[/TR]
[TR]
[TD]27500-30000[/TD]
[/TR]
[TR]
[TD]>30000[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[TABLE="width: 148"]
<tbody>[TR]
[TD][/TD]
[/TR]
[TR]
[TD]I'm assuming i need to add a calcualted column but I'm really struggling to get this to actually work and which DAX function to use, any help would be much appreciated.

Thanks very much

Rob[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]

 

[pete234]

I don't use banding in this way but I think the idea is to create a banding table and use FILTER.
I suggest this article although someone might come along and provide you a formula.
Alberto Ferrari : Banding with PowerPivot

Interesting he starts with the "quick" method but you can see the limitations as you have a lot of bands.

 

[robtop]

Thanks very much for that, seems to mostly work OK with just a few issues which hopefully I should be able to sort out

 

[MD610]

I think pete's right. Alberto's final solution is a great one for banding (I use it frequently) but can be a little tough to understand, especially if you are new to PowerPivot.

You might want to try the SWITCH() funtion which is not quite as elegant but easier to follow, I think, for a beginner.

The expression would be the column with your [Value]. Value 1 would the range of your first band [Value]>=0 && [Value]<=2500. Result 1 would be "0-2500". Then pretty much repeat the Value/Result pattern for every banding range.

This is less tedious and confusing than the nested IF statements in Alberto's quick solution but Alberto wrote this post before the SWITCH() function became available in PowerPivot V2.