I have a line and column chart in PowerBi that shows in the columns the sales in each month. i want to show in the line y-axis the total average of the year; I mean, a only value to get a straight line across my visual.
I want to get Something like this
I tried to modify my average measure to remove the month filter, because is what i think is affecting my line, but its still being modified by the filter (and i need to conserve that filter for the columns)
i tried these measures but none of them worked:
AVERAGEX( VALUES('Calendar'[Month]), CALCULATE([Sales]) )
CALCULATE(AVERAGEX(VALUES('Calendar'[Month]),CALCULATE([Sales])),REMOVEFILTERS('Calendar'[Year]) )
This is the data that I used for testing
date | value |
---|---|
2024/1/1 | 100 |
2024/2/1 | 200 |
2024/3/1 | 300 |
2024/4/1 | 400 |
2024/5/1 | 500 |
2024/6/1 | 600 |
2024/7/1 | 700 |
2024/8/1 | 800 |
2024/9/1 | 900 |
2024/10/1 | 1000 |
Then I created a calendar table and created relationship with fact table
At last, you can create a measure
MEASURE =
DIVIDE (
CALCULATE ( SUM ( 'Table'[value] ), ALL ( 'Table' ) ),
CALCULATE ( DISTINCTCOUNT ( 'date'[month] ), ALL ( 'date' ) )
)