Map - Tooltip - Top 1 Column value based on Highest Sales

[bkar81]

I have created a Map Visual and have placed "Customer Country" & "Customer State" in the Location and in the "Size" I have placed the [CY Sales]... I want to show the Top 1 Customer in the Tooltip who has the highest Sales in that State & Country.

When I drag and drop the [Customer Name] into the Tooltip, it is showing the First Customer ordered by ASC order and not based on the Max Sales in that State.  When I drill up, it has to show the Top 1 Customer having highest sales in that Country hovered (not clicked)

Is this achievable? If so, may I know how to achieve this?

I tried RANKX & FILTER functions

There is no Separate Customer Table, I have just 1 Fact Table. Tried creating a separate table as well

Cust_Tab = VALUES('Pricing'[Customer])

Columns available are -> [Customer Name], [CY Sales], [Customer_Country], [Customer_State]

Measure

Customer Rank by Sales = RANKX(

                                           ALL('Pricing'[Customer_Country], 'Pricing'[Customer_State], 'Pricing'[Customer]),

                                           [CY Sales], ,DESC,Dense)

Top 1 Customer = CALCULATE(

                               VALUES('Pricing'[Customer]),

                               FILTER(

                                           ALL('Pricing'[Customer_Country], 'Pricing'[Customer_State], 'Pricing'[Customer]),

                                           IF([Customer Rank by Sales] <= 1,

                                               [CY Sales],

                                               BLANK()

                                              )

                                          )

                               )

If I use this Top 1 Customer a Grid, it is showing correctly, but if I use the same in Map, it is showing only the First Customer of the Country even if I hover over the State.

As a temporary fix, I have used another Page as a Tooltip page and it is working...

But I want to know whether this can be done directly in the Map itself on HOVER functionality and NOT on CLICK (Filter) - even on Click, it is not working

Thank you well in advance...

[bkar81]

Will this return the Customer based on the Max Sales value or will it return the top 1 of all the Customers in that state sorted by descending order of Customer Name?

[v-kelly-msft · Accepted solution]

Hi  @bkar81 ,

I made a simple table for example:

State	Customer	Sales
A	a1	100
A	a2	200
A	a3	300
A	a4	400
A	a5	500
B	b1	600
B	b2	700
B	b3	800
B	b4	900

Then create a measure as below:

Measure = 
RANKX(ALLSELECTED('Table'),CALCULATE(SUM('Table'[Sales])),,DESC,Dense)

In the filter pane,choose measure is 1:

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[amitchandak]

@bkar81 , Try like

Top 1 Customer = CALCULATE(
maxx(VALUES('Pricing'[Customer]),'Pricing'[Customer])
FILTER(
ALL('Pricing'[Customer_Country], 'Pricing'[Customer_State], 'Pricing'[Customer]),
IF([Customer Rank by Sales] <= 1,
[CY Sales],
BLANK()
)
)
)