Calculate average between 2 columns, per year.

[fraitaan]

Hi, 

I have 3 columns.
- System Power

- Power

- Year

All in the same table. 

I want to calculate the average difference between System Power and Power, visualise the difference per year. 

How should I do it? 

I've tried using formulas like: 

Average Difference by Forecast Year =
AVERAGEX(
    VALUES('Data'[Year]),
    CALCULATE(
        AVERAGE('Data'[System Power]) - AVERAGE('Data'[Power])
    )
)

Also tried to make a new column called: "Power Difference" where I used M-code: 

[#"System Power"] - [#"Power"]

I tried to use average on it but I only get the same value over all the years: 

Thanks

[tamerj1]

@fraitaan 

Please make sure to use a year column which is in the same table or in a dimension table that filters this table. 

[eliasayyy]

hello please try 

average difference by year = 
VAR avgp = CALCULATE(AVERAGE('Table'[Power]),ALLEXCEPT('Table','Table'[Year]))
VAR avgsp = CALCULATE(AVERAGE('Table'[System Power]),ALLEXCEPT('Table','Table'[Year]))
VAR diff = avgp - avgsp
RETURN
AVERAGEX(VALUES('Table'[Year]),diff)

[fraitaan]

Hi, 

Thanks for answer

Still got the same value over all years

I need to see the difference between each year.

[eliasayyy]

as @tamerj1 stated, do you have a seperate table for year? or what did you include in the column chart which column