# Top 10 and Other using PowerPivot



## cr731 (Sep 24, 2014)

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?


----------



## GDRIII (Sep 24, 2014)

I'd play with RANKX() and an IF()


----------



## cr731 (Sep 24, 2014)

So I was able to get the ranking with RANKX, but if I try something like this,


```
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.


----------



## scottsen (Sep 24, 2014)

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.


----------



## cr731 (Sep 24, 2014)

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.



Sorry, I'm new to PowerPivots... what is the difference between a measure, calculated field and calculated column?


----------



## scottsen (Sep 24, 2014)

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.


----------



## cr731 (Sep 24, 2014)

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.



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


RegionCountrySalesRankCountry for RankEURA2505AEURB3001BEURC10016OtherASIAD2225D

<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.


----------



## scottsen (Sep 24, 2014)

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()


----------



## cr731 (Sep 24, 2014)

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]))
> ...



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."


----------



## scottsen (Sep 24, 2014)

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


----------



## cr731 (Sep 24, 2014)

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?


----------



## cr731 (Sep 24, 2014)

scottsen said:


> 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



I hate to be a pain, but now I get a "A circular dependency was detected:'Table1[CalculatedColumn1],'Table1[CalculatedColumn1],'Table1'[CalculatedColumn1)

I was trying this:


```
=CALCULATE(RANKX(Table1,[Total Sales]),Filter(Table1,Table1[Region] = EARLIER(Table1[Region])))
```

Where


```
[Total Sales] = SUM([Sales])
```

I did at first try doing the SUM([Sales] instead of [Total Sales], but then every line was ranked 1.


----------



## cr731 (Sep 24, 2014)

Alright, I'm getting closer!

I used this,

=rankx(filter(Table1,table1[region] = earlier(table1[region])),[sales])

And it works.  Now, I've discovered the following additional issues:

1. If a slicer is applied, the rankings do not recalculate... i.e. if previously ranked # 1 is filtered out of the data, it still acts as if it's # 1.

2. Not sure there is a resolution to this, but I would need ranks 1-10 to sort in descending order of sales, but "other" always needs to be at the bottom.  So the total Other might be greater than the country ranked #9-10, but it still needs to be shown last.  Is there a way to do this?


----------



## scottsen (Sep 24, 2014)

Nice job working through that.  I would have given up up RANKX, that thing hates me 

You can do the work "directly" something like:

```
=CALCULATE(countrows(table1), 
      filter(Table1, 
               Table1[Sales] >= EARLIER(Table1[Sales])  &&
               Table1[Region] = EARLIER(Table1[Region])
    )
  )
```

re: Re-calc, ...that is the way calc columns work.  They are *only* recalculated when the data is refreshed, and slicers have no impact.  If you want your slicer to impact a calculation, it must be in a measure.  However, your measures can't go on rows/slicers, so ... keep that in mind.

I think you will be able to power through your "I always want an Other" problem, but it will involve some tricky IF()'s.


----------



## GDRIII (Sep 25, 2014)

My first thought was to try and bludgeon "Other" into submission with a sort by table but, a tricky IF() is way more graceful.


----------



## cr731 (Sep 29, 2014)

GDRIII said:


> My first thought was to try and bludgeon "Other" into submission with a sort by table but, a tricky IF() is way more graceful.



How tricky of an IF are we talking about?  I cannot visualize what it would even need to begin with.


----------



## GDRIII (Sep 29, 2014)

Scott mentioned the tricky IF()

I was just being snarky but by bludgeon I would've tried an eleven row 2 column table with "Other" in the 11th slot and link it to a calculated column where 11 was the result for anything not 1-10...

I'm a hack though.


----------



## scottsen (Sep 29, 2014)

Can you get me the current version via dropbox/onedrive/googledrive/whatever ?   I lost track of what is on columns vs measures, etc.


----------

