Finding the Top 3 Highest value and highlighting in DAX

have the sale date and in that, we want to show the month-wise revenue in that the client wants to see the Top 3 highest revenue of the month in each year. For that, we want to write a DAX function.

Step 1:

Write a calculated table to get the Dates from the CalenderAuto() function also from the date function we need

Month =Calendar[date].[Month]

Step 2:

Get the Total Revenue in the measure

Total Revenue = Sum(SalesTable[Revenue])

Step 3:

Write the following DAX function

Top 3 Revanue =

var _Months = ALL(calender[month],calender[month number])

var _currentRevanue = [Total Revanue]

var _Ranking = RANKX(_Months,[Total Revanue],_currentRevanue,DESC)

var _result = IF(_Ranking<=3,”Green”,”Blue”)

return _result

Step 4 :

In the bar chart month as an axis and value as a revenue

The format the bar chat and then convert the data colour to function and select the measure you created.

Select the measure that we created in the base field

Now the bar chart will be shown the top 3 highest value revenue as Green and the rest all in blue

Follow us for more such topics and suggestions.Also, click the link below to follow us in LinkedIn https://www.linkedin.com/showcase/sphyzee-analytics/about/

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store