Bar Chart integration of expression

LouisJ — Mon, 11 Jul 2022 13:12:48 GMT

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)

topic Bar Chart integration of expression in App Development

Bar Chart integration of expression