powerbidax

fix a value for a line chart in Power BI


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:

  1. AVERAGEX( VALUES('Calendar'[Month]), CALCULATE([Sales]) )

  2. CALCULATE(AVERAGEX(VALUES('Calendar'[Month]),CALCULATE([Sales])),REMOVEFILTERS('Calendar'[Year]) )


Solution

  • 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' ) )
    )
    

    enter image description here