topic Calculate average between 2 columns, per year. in DAX Commands and Tips

Calculate average between 2 columns, per year.

fraitaan — Tue, 02 May 2023 07:55:14 GMT

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

Re: Calculate average between 2 columns, per year.

eliasayyy — Tue, 02 May 2023 08:57:24 GMT

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)

Re: Calculate average between 2 columns, per year.

fraitaan — Tue, 02 May 2023 09:29:26 GMT

Hi,

Thanks for answer

Still got the same value over all years

I need to see the difference between each year.

Re: Calculate average between 2 columns, per year.

tamerj1 — Tue, 02 May 2023 10:13:18 GMT

fraitaan

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

Re: Calculate average between 2 columns, per year.

eliasayyy — Tue, 02 May 2023 11:47:19 GMT

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