locked
DAX rank by percent value and show top 10 RRS feed

  • 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

Answers

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


    - www.pmOne.com -

    • Marked as answer by Daniel Lial Tuesday, April 16, 2013 1:04 PM
    Monday, April 15, 2013 4:10 PM
    Answerer
  • I'll try to find another way to get the results...thanks for your help Gerhard!
    • Marked as answer by Daniel Lial 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
    Answerer
  • Hi Gerhard, 

    thanks for your help...

    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 Daniel Lial Tuesday, April 16, 2013 1:04 PM
    Monday, April 15, 2013 4:10 PM
    Answerer
  • I'll try to find another way to get the results...thanks for your help Gerhard!
    • Marked as answer by Daniel Lial Tuesday, April 16, 2013 1:04 PM
    Tuesday, April 16, 2013 11:06 AM