CODEWITHSUNDEEP
powerbidax
Tadeo Lyle
Tadeo Lyle

Reputation: 17

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]) )

Upvotes: 0

Views: 74

Answers (1)

Ryan
Ryan

Reputation: 2437

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

Upvotes: 1

Related Questions