conditional sorting

[Myura]

Hi guys!
I have a data set which looks like this.

Company-----01/05/14-----01/05/15---01/05/16
company A-----1,984----------2.500---------3,000
company B-----1,984----------2.500---------3,000
company C-----1,984----------2.500---------3,000
company D-----1,984----------2.500---------3,000

I want to form three tables like this

Top 30% (of value)
01/05/14-----01/05/15---01/05/16
company A-----company A----company C
company C-----company G----company Q
company F-----company B----company Y

same way I need bottom 30% and middle 40%.

I just need to sort the names according to their value for each date.


Thanks in advance.

 

[scottsen]

Assuming we are talking power pivot and such... (and maybe even if not), I would reshape your data so that it is "long, not wide".

Company, Date, Value
--
CompA, 1/5/14, 1984
CompB, 1/5/14, 1985

This will make it easier to deal with.

You could probably add a calculated column fairly easy which uses RANKX to give each row a ranking, and from there it should be easy to assign "Top 30%" etc?

 

[Myura]

Assuming we are talking power pivot and such... (and maybe even if not), I would reshape your data so that it is "long, not wide".

Company, Date, Value
--
CompA, 1/5/14, 1984
CompB, 1/5/14, 1985

This will make it easier to deal with.

You could probably add a calculated column fairly easy which uses RANKX to give each row a ranking, and from there it should be easy to assign "Top 30%" etc?

Thank you so much for your help. I can reshape the table as you suggested. But after that using RankX formula, I'm not sure. Could you please explain me how it's done?.

Thank you so much for your time.

 

[scottsen]

This is probably the best reference on RANKX: RANKX-apalooza: Within and Across Groups, With Filters, Etc. - PowerPivotPro but even after reading that, calculated columns somehow makes this even weirder.

Try something like this as your calculated column:

=<br><span class="Keyword" style="color:#0070FF">RANKX</span><span class="Parenthesis" style="color:#969696"> (</span><br><span class="indent4">    </span><span class="Keyword" style="color:#0070FF">ALL</span><span class="Parenthesis" style="color:#969696"> (</span> Table1[Company] <span class="Parenthesis" style="color:#969696">)</span>,<br><span class="indent4">    </span><span class="Keyword" style="color:#0070FF">CALCULATE</span><span class="Parenthesis" style="color:#969696"> (</span><br><span class="indent8">        </span><span class="Keyword" style="color:#0070FF">SUM</span><span class="Parenthesis" style="color:#969696"> (</span> Table1[Value] <span class="Parenthesis" style="color:#969696">)</span>,<br><span class="indent8">        </span><span class="Keyword" style="color:#0070FF">ALL</span><span class="Parenthesis" style="color:#969696"> (</span> Table1 <span class="Parenthesis" style="color:#969696">)</span>,<br><span class="indent8">        </span>Table1[Company] = <span class="Keyword" style="color:#0070FF">EARLIER</span><span class="Parenthesis" style="color:#969696"> (</span> Table1[Company] <span class="Parenthesis" style="color:#969696">)</span><br><span class="indent4">    </span><span class="Parenthesis" style="color:#969696">)</span><br><span class="Parenthesis" style="color:#969696">)</span><br>