topic Re: Top Ranked in various metrics in DAX Commands and Tips

Top Ranked in various metrics

Tob_P — Mon, 04 Dec 2023 20:29:07 GMT

I have a number of current ranking measures which detail top salesperson, top selling region etc which are working well within a clustered column visual.

What I would like to do with each metric is display some additional information. So in best selling region, I'd like to display the region name (this is as a tooltip), but probably through concantenation, also display the best selling salesperson in that region and the total sales for that person. I have all these currently as measures along the lines of this...

Top Ranked Region =

CALCULATE(SELECTEDVALUE(tblRep_List[Region]),

TOPN(1,ALL(tblRep_List[Region]), [Amount Measure],DESC))

...and have tried some concatenation to merge the measures togeher but obviously I'm then displaying the correct highest selling region ,

but not the correct salesperson within that region.

Can anyone advise as to the best way to approach this?

Thanks

Re: Top Ranked in various metrics

Jihwan_Kim — Tue, 05 Dec 2023 04:16:06 GMT

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

I hope the below can provide some ideas on how to create a solution for your semantic model.

RANK function (DAX) - DAX | Microsoft Learn

TOPN function (DAX) - DAX | Microsoft Learn

CONCATENATEX function (DAX) - DAX | Microsoft Learn

Re: Top Ranked in various metrics

Tob_P — Tue, 05 Dec 2023 09:20:41 GMT

Thank you for replying and I can see that I could probably achieve an element of what I need with your solution, so thank you for that.

Here is a link to stripped back sample file

https://drive.google.com/file/d/1rCl91ci3u80OuT62kUsQgOt6lCOuxyiL/view?usp=sharing

What I would like to see if possible to achieve is utilising some of the measures I have (adpating them of course), to have the tooltip style in the first chart, blending together the best selling salesperson & team by daily sales. Unfortunately I need to keep the existing format of the tooltip as there are a number of other metrics I need to display, that cannot be superceded by the tooltip suggestion you have offered.