# DAX rank by percent value and show top 10

• ### Question

• I'm new on PowerPivot and I need some help. I'm trying to sort a pivottable by a measure that show a percent value and then show the top 10 rows only. The sort option in the context menu have worked and done my first step. When I select the top 10 filter, it shows the top 10 rows based on alphabetic order, sorted by the values that I choose early. I think it should shows the top 10 rows based on my percent values. I don't know if there is an error in my dax formula.

I tried to change some things but I didn't obtain the expect results. Can anyone help me? Thanks for your time.
Monday, April 15, 2013 3:31 PM

• this has nothing to do with your DAX, its a native feature of the pivot table

- www.pmOne.com -

• Marked as answer by Tuesday, April 16, 2013 1:04 PM
Monday, April 15, 2013 4:10 PM
• I'll try to find another way to get the results...thanks for your help Gerhard!
• Marked as answer by Tuesday, April 16, 2013 1:04 PM
Tuesday, April 16, 2013 11:06 AM

### All replies

• you can do a simple Value Filter to get the desired result

click the little dropdown-box on your rows --> Value Filter --> Top N --> Show "Top" "10" "items" by "<your percent measure>"

in addition you may also need to sort the results accordingly afterwards

this should do the trick

hth,
gerhard

- www.pmOne.com -

Monday, April 15, 2013 3:49 PM
• Hi Gerhard,

I already have tried this...it sort my table but with only the top10 in the alphabetic order...it ignores my percent measure...

Here is my formula:

=CALCULATE(SUM(Database[Sales]);
Database[Year]=SWITCH(MONTH(NOW());1;YEAR(NOW())-1;YEAR(NOW()));
Database[Month]=SWITCH(MONTH(NOW());1;12;MONTH(NOW())-1);
WC[Collection]="Collection1")

/CALCULATE(SUM(Database[Sales]);
Database[Year]=SWITCH(MONTH(NOW());1;YEAR(NOW())-2;YEAR(NOW())-1);
Database[Month]=SWITCH(MONTH(NOW());1;12;MONTH(NOW())-1);
WC[Collection]="Collection1")

Monday, April 15, 2013 4:03 PM
• this has nothing to do with your DAX, its a native feature of the pivot table

- www.pmOne.com -

• Marked as answer by Tuesday, April 16, 2013 1:04 PM
Monday, April 15, 2013 4:10 PM
• I'll try to find another way to get the results...thanks for your help Gerhard!
• Marked as answer by Tuesday, April 16, 2013 1:04 PM
Tuesday, April 16, 2013 11:06 AM