Forum Discussion
Map - Tooltip - Top 1 Column value based on Highest Sales
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
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...
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,
KellyDid I answer your question? Mark my post as a solution!
3 Replies
- amitchandak
Super User
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()
)
)
) 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-msftCommunity Support
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,
KellyDid I answer your question? Mark my post as a solution!
