Hi all!
I'm trying to get the top X performer without having to display all the previous ones using TopN.
For instance, if I click a region in a map (or click a region in a normal slicer) I want to see the number 2 sales performer.
I've read this topic about the same question and I adapted the code there to my case. I've made it work for one result (name of the sales agent) but not quite for showing their profit. I'm still learning DAX so I still struggle sometimes...
How to get the second top performer
I have a table called "Sales Agents" and another one called "Sales", so these are the 2 measures I have:
Agents ranked = RANKX(ALLSELECTED('Sales Agents'[Sales Agent Name]), CALCULATE(SUM('Sales'[Profit])))
And the one showing in this case Top 2 perfomer:
Agents filtered by rank = CONCATENATEX(FILTER(ADDCOLUMNS(SUMMARIZE(VALUES('Sales Agents'[Sales Agent Name]), 'Sales Agents'[Sales Agent Name]), "rk" , [Agents ranked])
, [rk] = 2), [Sales Agent Name])
With this I get the Name of the sales agent correctly, which is fine.
Now I'm trying to adapt this to show their profit, and I get the correct result but in text format. Is there a way to adapt the code to get the result as a number?
The first measure I left it the same. The second measure I adapted it like this:
Agents filtered by rank =
CONCATENATEX(FILTER(ADDCOLUMNS(SUMMARIZE(VALUES('Sales Agents'[Sales Agent Name]), 'Sales Agents'[Sales Agent Name]), "rk" , [Agents ranked]), [rk] = 2), [Total Profit])
I've tried to remove the concatenatex part as it's a string function but I'm getting an error and the measure doesn't get created.
Can you help me please?
Thank you very much in advance! 😊
