Average based on distinct values in another column

[Edoardo A]

Dear All,

For my project I need to create a bar-chart where category axis is the average of an aggregated sum and value axis is rowcount().

I can't find a way to calculate the average of an aggregated sum directly in the barchart.

In the attached dxp you can find the step required to calculate the final value (85,26).

I came to a solution using the calculate columns (highlighted in the dataset attached), but I need to calculate it directly in the bar-chart so it will change when filters are applied .

I tried to explain better. Using the following formula:

The average is done on the following values (and the result is 84,82):

83,50 83,50 83,50 83,50 83,50 83,50 83,50 83,50 86,14 86,14 86,14 86,14 86,14 86,14 86,14 86,14

So I tried to add DISTINCT but the result was still incorrect since the average is done for the following values (83,50+86,14)2 = 84,82

The average should be based on distinct values from "Period" column (83,50+86,14+86,14)3 = 85,26.

Can anyone help me to find a formula to obtain the last average

Thanks in advance!