Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Ranking not working correctly in map

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 = V...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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's avatar
Anonymous
Not applicable
6 years ago

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's avatar
Anonymous
Not applicable
6 years ago

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's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        Anonymous
        Not applicable
        6 years ago

        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