Average based on two columns with filter of third column in QLIKSense
I am trying to make an average on a bar chart, that is more dynamically visualized.
My table is like this: (it consists of more records, but the structure is this)
My bar chart is set up to show the 2018 values and 2019 values, based on a master dimension, where the viewer selects the subarea of interest. The bar chart then goes from showing all 2018 and 2019 values of all subareas, to only showing the specific ones, of the subarea selected.
In addition to this dynamic illustration, I would like to show an average of the selected subareas, based on the 2018 and 2019 values. E.g.:
I click on Newsletters, and the bar chart changes to show newsletter related values, including the average as a line of the two newsletter subareas; (131.612 + 416.541) / 2 = 274.076.
The sum of values, the values and the amount of elements divided to the sum of values changes depending on what subarea is selected.
The columns Name, Responsible and Comment are not relevant for this issue.
I have tried looking for a way to do that in the community, but haven't figured it out.