Map Colour-Coded by Category

[emilne]

I have a table that contains the total number of items assigned to a label (see below) and I am trying to create a shape map that fills in each location based on the label with the maximum. (So for this example, CAN=red for Apple, MEX=orange for Apricot, USA=yellow for Banana).

Country	Label	Subcategory	Total
CAN	Apple	A	33
CAN	Banana	B	22
CAN	Apricot	A	1
MEX	Apricot	A	55
MEX	Banana	B	22
MEX	Apple	A	6
USA	Banana	B	100
USA	Apple	A	8
USA	Apricot	A	99

I've tried calculating the rank for each label in Power Query and filtering the visual so that Rank equals 1, which works when all values are selected but stops working when I add a slicer that filters by category (since in some cases, the maximum value ranks as 2 overall but 1 within a category). I also tried calculating ranks for each category individually, but this doesn't work when selecting all or multiple categories.

I also tried creating a measure that gets the maximum value for each region dynamically but I can't figure out how to connect it to the labels for the legend so that they don't all show up as the first value (Apple).

How can I create a legend that indicates the maximum value dynamically? Is this possible in Power BI?

[emilne · Accepted solution]

Thanks, I wanted to apply the color to the legend so it would calculate the max value no matter which categories were selected. I was able to solve it filtering the visual on the measure Ranking==1: 

RankTotal = SUM('Census'[Total])

Ranking = RANKX(ALL('Census'[Label]), [RankTotal])

[Anonymous]

Hi, @emilne 

After reading through your requirements, I'm sorry to say that it wasn't possible to determine what effect you ultimately want to achieve, whether you want to apply the color to the legend or the background color or something else. What do you mean by dynamic legend effect? Thanks for your understanding.

Best Regards,
Yang
Community Support Team