Creating two straight average lines on a bar chart

[cookm01]

Hello,

I am currently trying to create two straight average lines for a bar chart, to represent the averages for two years on the chart.

The context (using dummy data): I have a bar chart which shows clients' costs as a % of income in 2023 and 2024. The bar chart is filtered to only show the data from 2024, but I want to have two lines that demonstrate the average from 2023 and 2024 for all customers going across this. The average is to be created as an aggregate (just adding them up and dividing by total number of clients). I currently get the average line on the bar chart but it is not straight (it is the average for each customer rather than the total year).

Please find attached dummy data and example of table without lines below.

TIA

Year	Customer	% of income
2024	Customer A	10%
2024	Customer B	40%
2024	Customer C	70%
2024	Customer D	20%
2024	Customer E	60%
2024	Customer F	50%
2024	Customer G	30%
2024	Customer H	50%
2024	Customer I	90%
2024	Customer J	80%
2023	Customer A	20%
2023	Customer B	40%
2023	Customer C	60%
2023	Customer D	30%
2023	Customer E	80%
2023	Customer F	90%
2023	Customer G	50%
2023	Customer H	10%
2023	Customer I	40%
2023	Customer J	60%

[Anonymous · Accepted solution]

Hi @cookm01 ,

I create a table as you mentioned.

Then I create a measure and here is the DAX code.

Measure = 
VAR _currentYear =
    MAX ( 'Table'[Year ] )
RETURN
    CALCULATE (
        AVERAGE ( 'Table'[% of income ] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Year ] = _currentYear )
    )

Finally you will see what you want.

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PhilipTreacy]

@Anonymous 

Not sure this is exactly what the OP asked for so not sure why it's been marked as the solution.

The bar chart is filtered to only show the data from 2024, but I want to have two lines that demonstrate the average from 2023 and 2024 for all customers going across this.

Regards

Phil

[PhilipTreacy]

@cookm01 

Try these 

2023 Avg = CALCULATE(AVERAGE('DataTable'[% of income ]), ALLEXCEPT('DataTable','DataTable'[% of income ]),'DataTable'[Year ] = 2023)

2024 Avg = CALCULATE(AVERAGE('DataTable'[% of income ]), ALLEXCEPT('DataTable','DataTable'[% of income ]),'DataTable'[Year ] = 2024)

regards

Phil