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.
I have a table of attendance counts for different events. Some weeks there are two events, other weeks there is just one event and occasionally there are no events. I want to calculate the 52 week rolling average attendance per week.
I tried this measure but it calculates the average attendance per event not per week.
Solved! Go to Solution.
If you want to calculate how many people visited (as many) events (as they are) per week and then rolling Average them only over weeks with events, this formula should deliver the desired result:
AvgAtt = IF ( ISBLANK ( SUM ( 'Total Attendance'[Count] ) ); // 4) Return value only if there has been an event BLANK (); AVERAGEX ( // 3) Average the weekly figures SUMMARIZE ( // 2) Group by week and sum all attendees CALCULATETABLE ( // 1) Get relevant period 'Total Attendance'; DATESBETWEEN ( 'Date Table'[Date]; LASTDATE ( 'Date Table'[Date] ) - 364; LASTDATE ( 'Date Table'[Date] ) ) ); 'Date Table'[Week]; "WeeklySum"; SUM ( 'Total Attendance'[Count] ) ); [WeeklySum] ) )
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
If you want to calculate how many people visited (as many) events (as they are) per week and then rolling Average them only over weeks with events, this formula should deliver the desired result:
AvgAtt = IF ( ISBLANK ( SUM ( 'Total Attendance'[Count] ) ); // 4) Return value only if there has been an event BLANK (); AVERAGEX ( // 3) Average the weekly figures SUMMARIZE ( // 2) Group by week and sum all attendees CALCULATETABLE ( // 1) Get relevant period 'Total Attendance'; DATESBETWEEN ( 'Date Table'[Date]; LASTDATE ( 'Date Table'[Date] ) - 364; LASTDATE ( 'Date Table'[Date] ) ) ); 'Date Table'[Week]; "WeeklySum"; SUM ( 'Total Attendance'[Count] ) ); [WeeklySum] ) )
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Thank you so much, That works perfectly - I just need to do some homework to understand what's happening in the formula (although your comments help greatly).
This a very simple subset of the data that shows attendance data over a five week period (1-7, 8-14, 15-21, 22-28 January, 29 January - 4 February):
Date, event type, count
01 January 2018, AM, 100
01 January 2018, PM, 110
08 January 2018, AM, 120
24 January 2018, PM, 130
31 January 2018, PM, 70
01 February 2018, AM, 80
I want to calculate the average attendance per week where there are any events. In this example, the average over the period is 152.5 (i.e. 610/4).
Does that help?
Hi,
Share a dataset and show the expected result.
Hi @mikemagill,
First of all your informationis not very clear about the dataset and the way you are calculating the count of the events, however if I can understand correctly you are counting the events and then want thhe average. You need to setup a AVERAGEX function in order for it to work.
Should look something like this:
Average Of Events = CALCULATE ( AVERAGEX ( Table, [Count] ), DATESBETWEEN ( 'Date Table'[Date], LASTDATE ( 'Date Table'[Date] ) - 364, LASTDATE ( 'Date Table'[Date] ) ) )
Not really sure if this works since this as to be adjusted to your dateset however this is the general formula.
Can you share some data sample or a copy of the PBIX file?
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering 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 |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |