Use average of a column as Legend in Choropleth map

[DG94]

Hi!

I need some help. I am trying to create a choropleth map of the average hours overtime per city on days we go to the city. Therefore I have a table 'Deliveries', with the columns 'City', 'Date', 'Driver', 'Overtime'.  I have a slicer visual on Deliveries[Date]. In my choropleth map I want to display for every city the average of [Overtime] within the selected date range.

Example: 

{"Amsterdam", "2024-04-01", "John", 2}

{"Amsterdam", "2024-04-01", "Henry", 1,5}
{"Amsterdam", "2024-04-02", "John", 0,5}

{"Amsterdam", "2024-04-03", "Henry", 1,25}
{"Amsterdam", "2024-04-04", "John", 0,75}
Daterange is 2024-04-01 to 2024-04-03. So average Overtime for Amsterdam would be (2+1,5+0,5+1,25)/4=1,3125

When I just put [City] as the Location for the map and [Overtime] for the map just shows the first record, so Amsterdam is displayd with 2 hours overtime, instead of 1,3125.

The choropleth map does not accept a measure, and when I created a summarized table the date range in the slicer visual is not taken in account. What could be the solution?

[Anonymous · Accepted solution]

Hi @DG94 ,

Here is the sample data I created based on the example you provided.

City	Date	Driver	Overtime
Amsterdam	4/1/2024	John	2
Amsterdam	4/1/2024	Henry	1.5
Amsterdam	4/2/2024	John	0.5
Amsterdam	4/3/2024	Henry	1.25
Amsterdam	4/4/2024	John	0.75

Create a measure that calculates the average overtime hours in each city based on the date range in Slicer.

Average Overtime = 
AVERAGEX(
    FILTER(Deliveries, Deliveries[Date] >= MIN(Deliveries[Date]) 
    && Deliveries[Date] <= MAX(Deliveries[Date])
    ), 
    Deliveries[Overtime])

I use the Filled Map visual, where I can drag measure to Tooltips.

According to your needs, "The average overtime time in Amsterdam when the date range is 2024-04-01 to 2024-04-03 is (2+1.5+0.5+1.25)/4=1.3125".

The final visual effect is shown below:

pbix file is attached.

If you have any further questions please feel free to contact me.

I would be grateful if you could provide me with sample data for testing, please remove any sensitive data in advance.

Or provide the pbix file to help you further, and remember not to log into your account when uploading the pbix file in Power BI Desktop.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!