Show top 5 items in a slicer with two hierarchies

[vbbang]

Hi, I have a pie chart with a hierarchy and the hierarchy is two level , first level is item urgent (Yes/No) and the second level is supporting team.

I have set up a pie chart with legends as above hierarchy and values as total number of incident tickets.

So when the pie chart visual loads , I am seeing urgent and non-urgent ticket counts and when you click on urgent and non-urgent sections in the pie chart , then you can drill down to see what are tickets with differrent support team.

What we would like to show is when I click on urgent and non-urgent sections in the pie chart, it should display me the top 5 support teams who has urgent and non -urgent tickets respectively. 

Hope it makes sense. 

[Anonymous · Accepted solution]

Hi @vbbang 

You can try the Tooltip function to realize your need . When you put your arrow on the corresponding category, it will show the Top5 supporting teams .Please follow my steps .

(1)Add a new page to display the Top 5 supporting teams . Create a measure to return the Top 5 teams info .

rank = RANKX(FILTER(ALL('Table'),[item urgent]=MAX('Table'[item urgent])),CALCULATE(MAX('Table'[incident tickets])),,DESC,Dense)

(2)In visual filter ,set the measure is less than or equal to 5 , so as to return the information of the top 5 .

So you will get a result like this :

(3)Then start to set tooltip for pie chart . For details, please refer to the link below .

https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-tooltips

When everything is set up, you can see an effect like this .

I have attached my pbix file , you can refer to it .

Best Regard

Community Support Team _ Ailsa Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[vbbang]

Hi, Thank you so much. You saved my Day !

Can you please let me know what is the issue with below measure ?

Rank columnmeasure = IF (

HASONEVALUE('Table'[supporting team] ),

RANKX ( ALLSELECTED ( 'Table'[supporting team] ),CALCULATE(COUNT('Table'[item urgent]) ) ))

We dont have supporting teams unique , they repeat.

[vbbang]

Hi, I tried this but received an error. Column "Measure" cannot be found or may not be used in this expression.

[amitchandak]

@vbbang , Create a TOP 5 measure for team

Top 5= calculate([Measure],TOPN(5,allselected(table[supporting team]),[measure],DESC), values(table[supporting team]))

Then use

if(isinscope(Table[supporting team]) ,  [Top 5] , [Meausre])

or

if(isinscope(Table[supporting team]) && isfiltered(Table[urgent ])  ,   [Top 5] , [Meausre])

refer

TOPN https://youtu.be/QIVEFp-QiOk

https://xxlbi.com/blog/new-dax-function-isinscope/

https://powerpivotpro.com/2013/03/hasonevalue-vs-isfiltered-vs-hasonefilter/