Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi All, I need help with some basic stuff. I fried my brain and hit the dead end.
So, how can I add simple average for the whole period and 3 weeks MA to my data to get the below in bold columns?
Week | Schedule | Unschedule | Ave Schedule | Ave Unschedule | MA 3 Weeks (Schedule) | MA 3 Weeks (Unschedule) |
1 | 100 | 100 | 125 | 124 | ||
2 | 110 | 110 | 125 | 124 | ||
3 | 120 | 120 | 125 | 124 | ||
4 | 130 | 130 | 125 | 124 | 330 | 330 |
5 | 140 | 140 | 125 | 124 | 360 | 360 |
6 | 60 | 100 | 125 | 124 | 390 | 390 |
7 | 160 | 110 | 125 | 124 | 330 | 370 |
8 | 170 | 120 | 125 | 124 | 360 | 350 |
9 | 180 | 130 | 125 | 124 | 390 | 330 |
10 | 80 | 180 | 125 | 124 | 510 | 360 |
11 | 430 | 430 |
Solved! Go to Solution.
Hi @tomgag
Based on my test, the "Ave Schedule" and "Ave Unschedule" provided by Greg_Deckler are right.
Then, Try the following formula to get calculated columns "3 weeks MA"
MA 3 Weeks (Schedule) = IF ( [Week] > 3, SUMX ( FILTER ( ALL ( 'Table' ), [Week] < EARLIER ( [Week] ) && [Week] >= EARLIER ( [Week] ) - 3 ), [Schedule] ) ) MA 3 Weeks (Schedule) = IF ( [Week] > 3, SUMX ( FILTER ( ALL ( 'Table' ), [Week] < EARLIER ( [Week] ) && [Week] >= EARLIER ( [Week] ) - 3 ), [Unschedule] ) )
Best Regards
Maggie
Hi @tomgag
Based on my test, the "Ave Schedule" and "Ave Unschedule" provided by Greg_Deckler are right.
Then, Try the following formula to get calculated columns "3 weeks MA"
MA 3 Weeks (Schedule) = IF ( [Week] > 3, SUMX ( FILTER ( ALL ( 'Table' ), [Week] < EARLIER ( [Week] ) && [Week] >= EARLIER ( [Week] ) - 3 ), [Schedule] ) ) MA 3 Weeks (Schedule) = IF ( [Week] > 3, SUMX ( FILTER ( ALL ( 'Table' ), [Week] < EARLIER ( [Week] ) && [Week] >= EARLIER ( [Week] ) - 3 ), [Unschedule] ) )
Best Regards
Maggie
The first two are easy:
Column Ave Schedule = AVERAGEX(ALL('Table'),[Schedule])
Column Ave Unschedule = AVERAGEX(ALL('Table'),[Unschedule])
The second two I don't understand because those look like sum's and not averages but probably something like:
Column MA 3 Weeks (Schedule) = SUMX(FILTER(ALL('Table'),[Week]<EARLIER([Week])&&[Week]>=EARLIER([Week])-2),[Schedule])
User | Count |
---|---|
100 | |
89 | |
82 | |
76 | |
71 |
User | Count |
---|---|
113 | |
104 | |
100 | |
73 | |
65 |