Average, Aggregate and Sum - Calculating the Average by Dimension
I am having this problem calculating the Average of VALUE to each STATE.
The formula that I used is avg(aggr(sum(VALUE)/count(HOSPITAL),STATE)) as shown in the image.
So as you can see this work Ok for some rows (First occurrence of HOSPITAL) but for other rows I am getting Null (even that I just let "count(HOSPITAL)" instead of "sum(VALUE)/count(HOSPITAL)" ) I will get Null.
The goal is that each row has the average calculated correctly like others rows (values in the bottom table for each State).
Someone know how can I solve this problem?
I am attaching the QVF that I used, is just an example with an inline.