Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
@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
User | Count |
---|---|
98 | |
90 | |
77 | |
71 | |
64 |
User | Count |
---|---|
114 | |
97 | |
95 | |
68 | |
65 |