Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All,
I'm looking for a solution for the problem below.
Previously, @v-jiascu-msft had a good solution for quite the same challenge here but I realized that my question was a bit poorly articulated.
So here it is again, with a twist:
I'm looking for the moving average of the highlighted facts. The problem is that I need to derive the moving average from percentages.
Please find the sample file at this link: https://www.dropbox.com/s/tntayh3i1w0d3jy/moving_average_dummy_v0.2.pbix?dl=0
And data at this one: https://www.dropbox.com/s/0y7ms7zap07gash/moving_average_dummy.xlsm?dl=0
Thanks,
breki
Solved! Go to Solution.
This MEASURE hopefully will get you the desired results except for 2016_W03
Moving Average= VAR Top3weeks = TOPN ( 3, CALCULATETABLE ( VALUES ( tbl_periods_all[Period Column] ), FILTER ( ALL ( tbl_periods_all ), tbl_periods_all[WeekNum] <= SELECTEDVALUE ( tbl_periods_all[WeekNum] ) && YEAR ( tbl_periods_all[Start Of Period] ) = YEAR ( SELECTEDVALUE ( tbl_periods_all[Start Of Period] ) ) ) ), CALCULATE ( SELECTEDVALUE ( tbl_periods_all[WeekNum] ) ), DESC ) RETURN IF ( COUNTROWS ( Top3weeks ) = 3, AVERAGEX ( Top3weeks, [Cat_1 + Cat_2 % of Total] ) )
I would think that if you combine these two techniques:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Rolling-Average/m-p/160720
https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149
That should get you there.
This MEASURE hopefully will get you the desired results except for 2016_W03
Moving Average= VAR Top3weeks = TOPN ( 3, CALCULATETABLE ( VALUES ( tbl_periods_all[Period Column] ), FILTER ( ALL ( tbl_periods_all ), tbl_periods_all[WeekNum] <= SELECTEDVALUE ( tbl_periods_all[WeekNum] ) && YEAR ( tbl_periods_all[Start Of Period] ) = YEAR ( SELECTEDVALUE ( tbl_periods_all[Start Of Period] ) ) ) ), CALCULATE ( SELECTEDVALUE ( tbl_periods_all[WeekNum] ) ), DESC ) RETURN IF ( COUNTROWS ( Top3weeks ) = 3, AVERAGEX ( Top3weeks, [Cat_1 + Cat_2 % of Total] ) )
@Zubair_Muhammad, this looks good and worked as well with some modifications. Thank you!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
105 | |
87 | |
73 | |
66 |
User | Count |
---|---|
122 | |
112 | |
98 | |
79 | |
72 |