Forum Discussion
Calculating an average in a matrix
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
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/1354591
https://community.powerbi.com/t5/Quick-Measures-Gallery/The-New-Hotness-Custom-Matrix-Hierarchy/m-p/963588#M428vote for Hybrid Table
https://ideas.powerbi.com/ideas/idea/?ideaid=9bc32b23-1eb1-4e74-8b34-349887b37ebc
1 Reply
- amitchandak
Super User
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/1354591
https://community.powerbi.com/t5/Quick-Measures-Gallery/The-New-Hotness-Custom-Matrix-Hierarchy/m-p/963588#M428vote for Hybrid Table
https://ideas.powerbi.com/ideas/idea/?ideaid=9bc32b23-1eb1-4e74-8b34-349887b37ebc