CODEWITHSUNDEEP
powerbiaveragedax
milanDD
milanDD

Reputation: 133

How to calculate yearly average per product?

I am new to Power BI and the language DAX. Therefore, I find it hard to do simple yearly average across different products.

I have tried a different combination of the functions AVERAGEX and CALCULATE without any luck so far.

My table: 

Date       | Product | Value
2014-05-06   Cheese    10
2014-08-11   Cheese    12
2015-04-11   Cheese    9
2014-01-22    Milk     4
2014-12-24    Milk     8

The output I try to create: 

Date       | Product | Value | Yearly_AVG
2014-05-06   Cheese    10         11
2014-08-11   Cheese    12         11
2015-04-11   Cheese    9          9
2014-01-22    Milk     4          6
2014-12-24    Milk     8          6

Lastly, I do not have a calendar table in the dataset.

Upvotes: 2

Views: 2814

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40204

For a measure, you can write something like this:

YearAvg =
VAR CurrProduct = VALUES ( Sales[Product] )
VAR CurrYear = YEAR ( MAX ( Sales[Date] ) )
RETURN
    CALCULATE (
        AVERAGE ( Sales[Value] ),
        ALLSELECTED ( Sales ),
        Sales[Product] IN CurrProduct,
        YEAR ( Sales[Date] ) = CurrYear
    )

Upvotes: 4

Related Questions