Count label rank frequency

[mifo123]

Hi,

I used the below DAX switch formula to label rank values, i.e. "Top 15", "Top20" & "Top30". I however want to count the frequency of the rankings in Power Bi. In excel I would simply create a pivot table and drag rankings to both row and values (see screen capture below).

How can I however count the frequency of the rank labels in Power Bi and DAX? Appreciate any help on this.

SWITCH(
          TRUE(),
          [Last rank]<16,"Top15",
          [Last rank]<21,"Top20",
          [Last rank]<31,"Top30",

[Smruti]

Hi ,

Hope below article will help you ,

http://www.dutchdatadude.com/power-bi-pro-tip-show-top-x-results-with-rankx-function/

One of the more frequent scenarios is listing the top X results, such as most profitable products, biggest customers, top 10 best selling stores, etc. Also doing a top X selection helps reduce clutter in charts: a lot of data points can work as noise and obscure the data points that really matter and make the biggest impact.

In this post I describe an approach to implementing these scenarios using Power Pivot’s RANKX() function.

Let’s start with a simple dataset consisting of products (P1…P20 in my sample), Cities, Sales Amount and Number of products sold:

After adding this table to the Power Pivot data model, we can use the RANKX() function to get the best selling products / cities etc. I added the following measures to my table:

Sum of Sales Amount:=SUM([Sales Amount])

Sum of Number Sold:=SUM([Number Sold])

Rank of products by sales amount:=RANKX(ALL(Sales[Product]);[Sum of Sales Amount])

Rank of city by number sold:=RANKX(ALL(Sales[City]);[Sum of Number Sold])

These measures allow me to determine the top selling products by sales amount and best cities by number of products sold.

Only thing left to do is to use a Pivot Table / Pivot Graph or Power View / Power Map visualization and display the results.

If you create a new Pivot Table and add the Product column and the ‘Rank of product by sales amount’ measure you get the following:

So how do we get the top 10 selling products by sales amount is a nice ordered fashion? Very easy, just a matter of the right sorting and filtering. Click on the little downwards pointing triangle button at Row Labels and choose ‘More Sort Options’. There I chose Ascending and then selected the rank measure:

Now the Pivot Table is sorted by rank with the highest ranking product at the top. Now, to filter out only the top ten, we press the same button again and choose Value Filters and then Top 10. Here I made the following selections:

This seems maybe a bit counter intuitive, but what this does is return the lowest ten ranks (which would be 1 to 10 or the highest ranking products). Alternatively I could have used a Lower Than or Equal To Value Filter with these settings to produce the same result:

And here it is: a top 10 of products by sales amount.