Ranking not working correctly in map

[Anonymous]

Hi,

I am trying to show the Top X sales person based on a slicer both in a table and on map. For that purpose I use the follwing measure as a visual filter:

Rang Salesstaff Map Sales Top X = 
VAR Ranking = RANKX(ALL(Salesstaff), [Total Sales],,DESC)
RETURN
IF(Ranking <='Top X'[TopX],'Sales'[Total Sales])

This measure works fine in the table filter, however when I use it in the map filter, it includes other salesstaff. It seems like that based on the ZIP code used in the map this meassure calculates an new ranking based on ZIP codes. Any suggestions on how I can fix this issue?

Thanks

[Anonymous]

Hi @Anonymous 

I build a sample table and add a rank column by calculated column.

Rank = RANKX('Table','Table'[vertragssumme],,DESC,Dense)

Table:

Then build a TopX table by rank column.

TopX = 
GENERATESERIES(MIN('Table'[Rank]),MAX('Table'[Rank]),1)

Use this table to build a slicer and build a measure.

Sales = 
var _selectvalue = SELECTEDVALUE(TopX[Value])
Return
IF(SUM('Table'[Rank])<=_selectvalue,SUM('Table'[vertragssumme]),BLANK())

Build a table visual and map visual to have a test.

Default they will show blank.

Select 10, two visuals will show top 10 values.

Or you don't need to build a slicer, you can drag rank into Filter on this page and show items when the value is less than or equal to 10, or other number you want and select apply filter.

You can download the pbix file from this link: Ranking not working correctly in map

Best Regards,

Rico Zhou

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

[Anonymous]

Thanks so much for your detailed solution @Anonymous !!!

However, when I use your calculated ranking column, it does not work correctly for my case, because the ranking is not dynmic based on other slicer. How would your solution work, if you had a user table and sales table with date of sale where the ranking needs adjusts based on month and year slicers?

[Anonymous]

Hi @Anonymous 

I build a new table add Year column. If you have date value in your table, you can add calculated column to get the Year and Month column.

Year = Year(Table(Date)) && Month = Month(Table(Date))

Then build a Rank measure it will dynamic by your Year and Month Slicer.

Rank = RANKX(ALLSELECTED('Table'),CALCULATE(SUM('Table'[vertragssumme])),,DESC,Dense)

Build a new TopX Table:

TopX = 
GENERATESERIES(MINX('Table',[Rank]),MAXX('Table',[Rank]),1)

New Sales Measure:

Sales = 
var _selectvalue = SELECTEDVALUE(TopX[Value])
Return
IF([Rank]<=_selectvalue,SUM('Table'[vertragssumme]),BLANK())

Build a table visual and Map visual to see our result.

Default:

Select 2019 and get the Top 3 Sales:

Select 2020 and get the Top 10 Sales:

You can download the pbix file from this link: Ranking not working correctly in maps

Best Regards,

Rico Zhou

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

[Anonymous]

Thanks for the updated solution @Anonymous However, your suggested solution has the same issue as my solution.

When I select the Top 5, Germany shows up with 100k in the table, which is correct. But in the map Germany shows up with 150k, because it still takes all values from the sales table in the selected year into consideration. How can I fix that?

[Anonymous]

Hi @Anonymous 

I change the Rank and Sales Measure as below.

Rank = RANKX(ALLEXCEPT('Table','Table'[Year]),CALCULATE(SUM('Table'[vertragssumme])),,DESC,Dense)

Sales = 
var _selectvalue = SELECTEDVALUE(TopX[Value])
Return
CALCULATE(SUM('Table'[vertragssumme]),FILTER('Table',[Rank]<=_selectvalue))

Other steps are the same as before.

Result:

You can download the pbix file from this link: Ranking not working correctly in maps

Best Regards,

Rico Zhou

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

[Anonymous]

Thanks for the promt reply @Anonymous 

I tried to get it to work for my scenario, but because I have a seperate date table, a sales table and a product table, I am unable to replicate your solution. Can you please have a look at the updated data model and update your solution?

Case_Ranking not working correctly in map 

[Anonymous]

Hi @Anonymous 

Could you share your pbix file with me by your OneDrive for Business again? I can't get access to it. Or if you have solved your problem, kindly share your workaround and mark it as a solution. More people will benefit from it. 

Best Regards,

Rico Zhou

[Anonymous]

@AnonymousThanks for your help. I have updated the link to the file.

[Anonymous]

Hi @Anonymous 

Please share your pbix file from your OneDrive for Business. Due to I don't have a permission to the pbix file you have shared with me.

Best Regards,

Rico Zhou

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

[Anonymous]

Hi @Anonymous Unfortunatly, my employer has disabled the link sharing with people outside of the organization. You should be able to download the link by pressing the download button on the top right:

[Anonymous · Accepted solution]

Hi @Anonymous 

I change your Rank measure and achieve your goal.

New Rank Measure:

Rank = RANKX(ALL('Sales Staff'),CALCULATE(SUM(Sales[Sale])),,DESC,Dense)

Result:

Best Regards,

Rico Zhou

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

[Anonymous]

Works perfectly, thanks so much @Anonymous!

[amitchandak]

@Anonymous ,

You can try top N, example

Top 10 City Rank = CALCULATE([Sales],TOPN(10,all(Geography[City]),[Sales],DESC),VALUES(Geography[City Id]))

assume this the rank

City Rank = RANKX(all(Geography[City]),[Sales])

these are option for filter, example 

Rank Top 10= CALCULATE(if([City Rank]<=10,[Sales],BLANK()) ,VALUES(Geography[City]))  // this might not work
Rank Top 10 1 = sumx(VALUES(Geography[City]),if([City Rank]<=10,[Sales],BLANK()) )
Rank 2nd top = sumx(filter(VALUES(Geography[City]),[City Rank]=2),[Sales] ) //=2

refer

https://databear.com/power-bi-dax-topn-function/

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415

[Greg_Deckler]

@Anonymous - Your IF statement doesn't seem complete.

[Anonymous]

@Greg_DecklerPlease elaborate. Based on my knowledge, a true/false statement and an instruction on what to do when the value is true should be sufficient.

[Greg_Deckler]

@Anonymous - Apparently you are correct, in that case it will automatically return blank if false. For some reason I thought it would never allow me to not have the false statement.

Your ALL should prevent recalculation at the zip code level.

[parry2k]

@Anonymous yes IF statement is correct. I guess you are using what-if parameter to determine ho may top x staff members you want, correct?

[Anonymous]

correct @parry2k 

[parry2k]

@Anonymous well others have provided the solution and see if it works, if not then reply it here and can look into it. Cheers!

[Anonymous]

This is the top 10 table, where the measure works correctly.

Markus, Reinhard and Peter show up on the map.

I believe this is because their total based on the zip still gets them into the Top 10, but the ZIP code should not be considered for this ranking.

Any idea @parry2k, @Greg_Deckler , @amitchandak?