Measure to calculate average based on 1 filter only igonring anything else

[casperholtfarve]

Hi there,
I have been running around with this problem for some time now and can't seem to find the solution.
I have the following visuals:

In the line chart on the right, I want to achieve the following:

I want a dot (later when multiple years will be applied, it should become a line obviously) to show the average results of the values on the left. No matter if a specific bar has been selected or not.

And then I want a second dot, to sho the actual selection. 

The results of the bar chart is based on a specific question from a filter slicers. An these might change.

So I want the measure to look at this filter only´.

I have tried the following:
Creating a measure for the general average:

Avg result =
AVERAGE(table[resultscolumn])

This I thought should be in the values field of the line chart. 

Then I tried to create another measure, which I aslo thought should go into the values of the line chart, but this measure should be the one ALWAYS showing the average of the selected results based on the specific filter question:


Avg Result ALLEXECPT =
CALCULATE(
   [Avg result],
   ALLEXCEPT('tablecontainingquestions', 'tablecontainingquestions'[questioncolumn]) 
)

At first glance it looks fine, but when I try to select a value in the bar chart it doesnt work it shows the exact same as the avg result measure. 


Any thoughts on how I might be able to achieve this?

[v-kkf-msft]

Hi @casperholtfarve ,

Not sure about the structure of your model, I created the following tables and inter-table relationships. 

Then create the measure:

Avg Result ALLEXECPT = 
CALCULATE (
    [Avg result],
    KEEPFILTERS ( Dates[Year] = MAX ( Dates[Year] ) ),
    ALL ( 'table' )
)

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MFelix]

Hi @casperholtfarve ,

The ALLEXCEPT remove all the filter from the calculations except the ones you refer on the expression, so in this case since you are keeping the values on the filter expression of the column you use for the chart, the value that you get from the average and from the ALLEXCEPT function is always the same in this case you should use the AVERAGE for the values that are filtered by the bar chart and change the ALLEXCEPT formula to keep the filter of the years:

Avg Result ALLEXECPT =
CALCULATE(
   [Avg result],
   ALLEXCEPT('tablecontainingquestions', 'tablecontainingquestions'[YearColumn]) 
)