Bar Chart integration of expression

[LouisJ]

Hello, I have an excel sheet with this format :

I need to make a bar chart representing the number of Reports ( count(ID) ) for one category per location devided by the monthly measure.  I have no problem when restricing the date to one month, but when I use multiple months problems arrise.

For this to work, I need to count the corresponding number of reports, then devide them by the sum of monthly measures per month (But only 1 per location and per month !!!!)

So for example, if I did it for location 1 in category A, I would have 4/(1.8+1.8), and not devided by 4*1.8 since 4 reports are from only 2 distinct months and so the monthly measure needs to be counted only once per month.

I have come up with this formula :

Count(if(match([Category],'A'),[ID])) / 

sum(aggr(avg(if(match([Category],'A'),[Monthly Measure])),[Month]))

This formula works perfectly if I place it in a KPI block or a text block, but for some reason when I place it in the bar chart it groups everything into one location. (The Bar Chart dimension is simply put on Location)

What would the problem be here ? Any alternative expressions to do this correctly ?

(edit: simplified the formula with avg aggregation, it still works in KPI block but not bar chart measure)