Forum Discussion

cmduke87's avatar
cmduke87
Regular Visitor
3 years ago

Ranking Matrix Country by Sales

I have done quite a few searches on this topic and there is lots of information on it. Unfortunately I am new to power bi and really struggling to understand how to take someone elses solution and mold it into mine. What I am looking for is to have a matrix chart with my machine models in the row and my countries ranked across the top. I have 153 countries so ideally I would like to show my top 10 and then put all the others into a category named other so that I see my total sales with top 10 counties and the rest in other. I also have quite a few splicers on the page (business/region/year) and am hoping to make sure it works with those.

 

What I have so far:

Rank NST Country Descr = RANKX( ALLSELECTED( Summary[Buyer Country Description] ) , CALCULATE( SUM( Summary[Final NS&T] ) ) )     This seems to work if I pull into a matrix as a column but I want the ranking to be hidden if that makes sense.
 
Appreciate any help on this and please let me know if I wasnt clear on anything!
 
 

2 Replies

  • Karthikstark's avatar
    Karthikstark
    Frequent Visitor
    3 years ago

    you can use a calculated coumn to give your countries a rank and use sort by column function to sort countries in the matrix visual. To group other countries that do not fall in your top 10 category use grouping function and manually select the countries and group  them together

    • cmduke87's avatar
      cmduke87
      Regular Visitor
      3 years ago

      What am I missing on the ranking formula? 

      Country NST RANK Col = RANKX(ALL(Summary[Buyer Country Description]), SUMX(RELATEDTABLE(Summary), [Final NS&T]))
       
      Its only returning 1 for everything and then the last one is 153. I was thinking it would be straightforward since both values are on the same Summary table.