Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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...
Solved! Go to 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!
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?
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!
@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()
)
)
)
User | Count |
---|---|
98 | |
91 | |
84 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
66 |