Forum Discussion
Creating two straight average lines on a bar chart
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% |
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.
3 Replies
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
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.
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
