Top 10 and Other using PowerPivot

[cr731]

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]

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]

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]

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]

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]

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]

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]

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