Forum Discussion

fraitaan's avatar
fraitaan
Frequent Visitor
3 years ago

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: 

 


Thanks

4 Replies

  • eliasayyy's avatar
    eliasayyy
    Icon for Memorable Member rankMemorable Member
    3 years ago

    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's avatar
      fraitaan
      Frequent Visitor
      3 years ago

      Hi, 

      Thanks for answer

      Still got the same value over all years


      I need to see the difference between each year.

      • eliasayyy's avatar
        eliasayyy
        Icon for Memorable Member rankMemorable Member
        3 years ago

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

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    fraitaan 

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