Forum Discussion
Power Pivot - calculation using Max value
Hello, I have the report and need to calculate the average attendees per day. I have needed to apply the MAX function to No Weekdays to reflect the correct vale. Sounds so simple! But the results are bizarre.
The measure I used is =[Sum of Attendees]/[Max of No Weekdays].
help correcting this gratefully received
| Month | Weekday | SUM Attendees | MAX No Weekdays | Avg |
| May | Mon | 61 | 21 | 165.4761905 |
| Tue | 200 | 21 | 165.4761905 | |
| Wed | 125 | 21 | 165.4761905 | |
| Thu | 118 | 21 | 165.4761905 | |
| Fri | 92 | 21 | 165.4761905 |
You say "the results are bizarre," which certainly would appear to be the case. What you don't say, however, is exactly which results are bizarre. Now, clearly the repeated number under the heading "Avg" needs to be questioned.
But how about the repeated number 21 under "MAX No Weekdays"? First of all, what is it (supposed to be) the maximum OF? Maximum of attendees on a Monday, Tuesday, ...etc? Maximum number of weekdays in the month? It seems equally bizarre that the same MAX number would appear in each row, as that the average would be the same in each row.
You know what your raw data contains. We don't. I realize you're the one asking for help, but it'd be easier to diagnose what's going on if we had a more complete picture of what the raw data are, what results might be reasonable in those last two columns.
Is it possible for you to post the actual spreadsheet on OneDrive or GoogleDrive (or some comparable cloud service), and then post a link and grant access to it?
