Top 10 and Other using PowerPivot

C

cr731

Well-known Member
Joined
Sep 17, 2010
Messages
611
I am trying to prepare a summary of Top 10 countries by region based on sales and also show the remaining items grouped into 'Other'. I was thinking a calculated column in a PowerPivot could accomplish this, but I cannot figure out the syntax. Basically what I want the formula to do is: If the country on that line is ranked in the top 10 for that region, then return that country's name, otherwise, return "Other." So for some examples,

Say for the Europe region we have Germany and France. If Germany is ranked # 4 for sales, this column would return Germany. If France is ranked # 14, it would return "Other" ... this way, anything ranked over 10 would be grouped into Other.

So,

1. Is this the best / easiest way to accomplish a "Top 10 + Other" summary?

2. What would the formula need to be?
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Sort by date Sort by votes
So I was able to get the ranking with RANKX, but if I try something like this,

Code: 
IF([Country Rank within Region] > 10,[Country],"Other")

I get:

The value for 'Country' cannot be determined. Either 'Country' doesn't exist, or there is no current row for a column named 'Country'.

I suspect this is because it's trying to apply this at the Region level, so if I have

Europe
Germany
France

At the Europe level, this formula is invalid... I'm stumped how to overcome that.
 
Upvote 0
fyi, from a "best practice" standpoint, I always preface column names... with a table name. Otherwise, I look at your formula and have no idea if I am looking at a measure (calculated field), or a column name.

Your IF() above... I would expect it to be a calculated column, on your table that has Country names.
 
Upvote 0
scottsen said:
fyi, from a "best practice" standpoint, I always preface column names... with a table name. Otherwise, I look at your formula and have no idea if I am looking at a measure (calculated field), or a column name.

Your IF() above... I would expect it to be a calculated column, on your table that has Country names.
Click to expand...

Sorry, I'm new to PowerPivots... what is the difference between a measure, calculated field and calculated column?
 
Upvote 0
Because Microsoft hates me... they renamed Measures (2010) in Calculated Field (2013). Those are formulas that are computed dynamically at run time and generally are used in the Values area of your pivot table. They operate on a "set" of rows and return a value (so, using SUM() on a column of numbers).

A calculated column is just another column in your power pivot table, that happens to be calculated at data refresh time, typically based off other columns in your table. It is calculated just 1 row at a time.
 
Upvote 0
scottsen said:
Because Microsoft hates me... they renamed Measures (2010) in Calculated Field (2013). Those are formulas that are computed dynamically at run time and generally are used in the Values area of your pivot table. They operate on a "set" of rows and return a value (so, using SUM() on a column of numbers).

A calculated column is just another column in your power pivot table, that happens to be calculated at data refresh time, typically based off other columns in your table. It is calculated just 1 row at a time.
Click to expand...

Thanks for that. I think I need calculated columns. My (simplified) table looks like:

RegionCountrySalesRankCountry for Rank
EURA2505A
EURB3001B
EURC10016Other
ASIAD2225D

<tbody>
</tbody>

I need rank to be the rank of the country within the region and then country for rank is, if rank > 10, "Other," else, Country of the row.

For rank, I tried: RANKX(Table1,Table1[Sales]) but this does not limit the rank to the region. I tried

RANKX(FILTER(Table1,Table1[Sales]),Table1[Sales) but it still doesn't work.
 
Upvote 0
Your intuition is correct, you need a calc column, because you are going to want to put them on rows/columns of your pivot table, and you can not do that with measures.

I would expect your calc column to look something like...
=CALCULATE(RANKX(Table1, Table1[Sales]), FILTER(Table1, Table1[Region] = EARLIER(Table1[Region]))

You technique of passing FILTER into the first param to RANKX is also good, I just feel more comfortable with CALCULATE() :)
 
Upvote 0
scottsen said:
Your intuition is correct, you need a calc column, because you are going to want to put them on rows/columns of your pivot table, and you can not do that with measures.

I would expect your calc column to look something like...
=CALCULATE(RANKX(Table1, Table1[Sales]), FILTER(Table1, Table1[Region] = EARLIER(Table1[Region]))

You technique of passing FILTER into the first param to RANKX is also good, I just feel more comfortable with CALCULATE() :)
Click to expand...

With that, I get the following error:

"The value for column 'Sales' in table 'Table1' cannot be determined in the current context. Check that all columns referenced in the calculation expression exist, and that there are no circular dependencies. This can also occur when the formula for a measure refers directly to a column without performing any aggregation--such as sum, average, or count--on that column. The column does not have a single value; it has many values, one for each row of the table, and no row has been specified."
 
Upvote 0
That ridiculous error always means "you forgot an aggregation". Instead of Table1[Sales] you want to use a [Total Sales] measure, that is =SUM(Table1[Sales]).

You can TRY putting SUM(Table1[Sales]) and that might work, but I suspect you would need to wrap the SUM() inside a CALCULATE() for complicated reasons :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Filtering Table into Multiple Columns
Replies
4
Views
334
shaowu459
shaowu459
RICH937
  • Question
How to create Running Totals on multiple non-date values and conduct filtered analysis on the results. Which Power Tool is the more efficient choice?
Replies
3
Views
165
RICH937
RICH937
G
  • Question
Using dax for top 20% in each category
Replies
0
Views
604
Guitarmageddon
G
P
  • Question
PowerPivot - Syntax error message because of separator (";" or ",") Getting error either from "Formula checker" or from MdxScript
Replies
3
Views
5K
GraH
GraH
J
  • Solved
Alternative to using the Choose function
Replies
8
Views
105
awoohaw
awoohaw

Forum statistics

Threads
1,214,946
Messages
6,122,401
Members
449,081
Latest member
JAMES KECULAH

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top