Calculate filtered averages - power bi

Question

I have a table with broker names, the values they provide and the years they provided them in.

Broker	Value	Year
A	100	2020
A	200	2021
B	400	2020
B	500	2021

On my dashboard, there is a single select slicer for the Broker. I would like to have a line and stacked column chart. The column would have the slicer selected broker Value by Year. The line i would like is an average Value of A & B by year. So, suppose i select A on my slicer, then the visual should have two columns at 100 and 200 for year 2020 and 2021 respectively. The line should show 250 and 350 respectively.

I have tried creating a measure called consensus

Consensus = CALCULATE(SUM(Broker[Value])/DISTINCTCOUNT(Broker[Broker]), ALLSELECTED())

This gives me the average across all years (300) and plots them irresptive of the year.

How do I get my measure to take account of the X axis (Year).

Thank you for your help.

Answer 1

How about using this measure:

Consensus = 
CALCULATE(
    DIVIDE(
        SUM(Broker[Value]),
        DISTINCTCOUNT(Broker[Broker])
    ),
    ALLEXCEPT(Broker, Broker[Year])
)