Power BI Ranking

[sumitsingla12]

Hi Folks,

Need expertise for one of the problem statement.

I have data shown below. Where we have year , states and sales.

Output expected is also shown , which is to have yearly view with top4 values within each year should be shown below, each value should be shown in separate columns, 

Tried using rank but no success. Please share your expertise.

   

[sumitsingla12]

Hi @SamWiseOwl , 

Thanks much , it is working for me,

Just curious to know how Power BI is executing this.

• For every year in my matrix Power BI will execute this calculation?
• In second step are we just creating an internal table as you are using this same in next step?
• how Maxx function is being executed?

I know am asking too much but this will help me understand as to how this calculation is executing.

Thanks again for this help. 

[SamWiseOwl]

Hi @sumitsingla12 ,

My pleasure, if you could mark it as an answer that would be appreciated!

• Correct the calculation is run with modified filters on each row. Check out SQLBI on youtube for excellent videos on behind the scenes stuff.
• Variables allow you to breakdown lots of nesting into steps, the first variable captures which rank column we are in and applies that as a filter to the DataTable.
• Maxx is taking in the filtered table, returning the largest number that remains in that column

If you search up on YouTube - Wise Owl Training we have a free video course on DAX, might be worth a watch! 

[sumitsingla12]

Thanks @SamWiseOwl . Great Help, I will go through youtube videos.

[SamWiseOwl · Accepted solution]

Hi @sumitsingla12 ,

Assuming the desired output is a visual, we need a way to filter each column by the value of the rank. Using EnterData create a table with a column called Rank and enter the values 1 through 4.
Put this in your Columns section of a Matrix and the Year in the rows (this will error for now).
Now create a measure that will read the Rank value and use this as a filter and put it in the values section of the visual:

Rank by sales =
VAR curRank =
SELECTEDVALUE ( 'Rank Table'[Rank] ) --Holds the single rank value in column
VAR SalesYear =
FILTER (
'Data Table',
--Filtered by Year in output visual
RANKX (
--Generate rank for each row in DataTable
'Data Table',
[Sales] --Sort by sales
,
,
DESC --largest sales first
,
DENSE -- If 2 values have 1st place, 3rd value gets 2nd place
) = curRank
) --Filter to visual row year
RETURN
MAXX ( salesyear, 'Data Table'[Sales] )
--Return highest rank