Skip to main content
|

PowerPivotPro's FAQ Site

Go Search
  
PowerPivotPro's FAQ Site > The Great PowerPivot FAQ > How do I group numbers in a PowerPivot PivotTable like I can in an Excel PivotTable?  

The Great PowerPivot FAQ: How do I group numbers in a PowerPivot PivotTable like I can in an Excel PivotTable?

Title

How do I group numbers in a PowerPivot PivotTable like I can in an Excel PivotTable? 

Answer

Grouping numbers, or discretization as it is known in BI and scientific circles, is available in Excel created PivotTables but not in PowerPivot created PivotTables.
 
You can simulate Excel type number grouping by creating a calculated field in a PowerPivot table. For example, suppose you have numbers in a table that range from 1 to 100 and you want to group by 10. In the following examples, I'm using a table called Number Groups which has a column named Num. 
 
1) If you want to start at zero i.e. 0-9, 10-19 etc, enter a formula like:
 
=FLOOR('Number Groups'[Num],10)&" - "&CEILING('Number Groups'[Num],10)+IF(MOD('Number Groups'[Num],10)>0,-1,10-1)
 
2) If you want to start at one i.e. 1-10, 11-20 etc, enter a formula like:
 
=FLOOR('Number Groups'[Num],10)+IF(MOD('Number Groups'[Num],10)>0,1,-(10-1))&" - "&CEILING('Number Groups'[Num],10)
 
3) 2) If you want to start at ten i.e. <10, 10-19 etc, enter a formula like:
 
=IF('Number Groups'[Num]<10,"<"&10,FLOOR('Number Groups'[Num],10)&" - "&CEILING('Number Groups'[Num],10)+IF(MOD('Number Groups'[Num],10)>0,-1,10-1))
 
You would typically hard-code the "bucket" (10 in this case) when you have a good idea of the expected range. You can calculate a bucket based on the smallest number in the range. In the above example range, you can create another caluclated field (call this "Bucket") and use the formula =ROUNDUP(MIN('Number Groups'[Num]),-1), which in this case rounds the minimum number in the range (1) to the next power of 10 (10 in this case). You can then substitute 'Number Groups'[Bucket] for the hard-coded numbers in the above formulas.
 
If you need to use a formula to group by a factor other than 10, you can use the MROUND function instead of ROUNDUP.
 

Client/Server

Client 

Topic

F - Calculations (DAX) 

Purpose

Information 

Answered?

Yes 

Contributor

Colin Banfield 

Título

 

Resposta

 

Frage

Wie gruppiere ich Zahlen in PowerPivot so wie ich es in Excel kann? 

Antwort

 

preRTM

 
Attachments
Created at 1/20/2010 10:56 PM  by Colin 
Last modified at 4/23/2010 1:32 PM  by Jochen Juelke