In this writing, I want to share how I use TOPN DAX function in order to only visualize the most wanted categories, instead of visualizing all categories. Additionally, if possible, I also want to break ties if there is shown any.
The question came out from the Power BI Community recently, and the link is down below.
As seen in the above figure, there are total 15 categories. Let's say, I want to only visualize top 3 categories by count. Firstly, I try to write a measure like below. Comments inside the measure is written in Red.
Top three count measure: = CALCULATE ( [Count measure:], // This is a measure that just shows the number of count KEEPFILTERS ( TOPN ( 3, ALL ( Data[Category] ), [Count measure:], DESC ) ) ) // KEEPFILTERS function is used in order to show top three categories in a visualization with correct numbers for each category (=maintain the filter context), otherwise, the total sum number of top three appears everywhere
Even, I specifically write 3 inside TOPN function, five categories are shown because there are ties in the result.
However, there is a way to add more conditions inside TOPN DAX function if you want to break ties by other options, for instance, by considering sales measure as a secondary option.
Top three count measure considering sales measure: = CALCULATE ( [Count measure:], KEEPFILTERS ( TOPN ( 3, ALL ( Data[Category] ), [Count measure:], DESC, [Sales measure:], DESC // you can try ASC to create reverse order for the secondary option ) ) )
Simply adding more expressions inside the TOPN DAX function, it works beautifully as you expected.
I hope this post helps you enjoy using TOPN.
Comments