Calculating an average in a matrix

[XikinhoXK]

Hello,

I'm having trouble doing something that at first glance seems quite straightfoward. I have the following table:

Each value is the total sum calculated from the data for the respective category and date. I would like to have an extra column containing the average for the last 12 moths but when i try to do it il always ends up adding 12 extra columns with the average for each month. If it works properly the values should be 2,9 for WIP and -1,4 for UI.

Any clues on how to achieve this?

Thanks 

[amitchandak · Accepted solution]

@XikinhoXK , You can get rolling Avg like

12 Month Avg = CALCULATE(AverageX(Values('Date'[MONTH Year]),calculate(Sum('Table'[Value)))
,DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))

You can use isinscope to replace total with avg

if(isinscope(Date[Month Year]), sum('Table'[Value) ,[12 Month Avg] )

or hybrid display

if you are looking for a Hybrid display with Matrix Column and measure
https://community.powerbi.com/t5/Community-Blog/Creating-a-custom-or-hybrid-matrix-in-PowerBI/ba-p/1...
https://community.powerbi.com/t5/Quick-Measures-Gallery/The-New-Hotness-Custom-Matrix-Hierarchy/m-p/...

vote for Hybrid Table
https://ideas.powerbi.com/ideas/idea/?ideaid=9bc32b23-1eb1-4e74-8b34-349887b37ebc