cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
fraitaan
Frequent Visitor

Calculate average between 2 columns, per year.

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: 

fraitaan_0-1683014012069.png

 


Thanks

4 REPLIES 4
tamerj1
Super User
Super User

@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
Super User
Super User

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)

Hi, 

Thanks for answer

Still got the same value over all years

fraitaan_0-1683019744472.png


I need to see the difference between each year.

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

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors