1 of 1 people found this helpful
Depending how your table is set up, you may be able to turn on grand total, which will actually average across the entire data set. It that doesn't work, then total(avg([values])) should.
Thanks for the guidance. I am not saying that they didn't work, simply I don't know what I'm doing with this. My fear is it is one of those so close to your eyes in front of you that you can't see it. I have attached a set of dummy data with the thirteen questions showing an average for each one. All I need is to average those averages (ignoring the questions that have a NULL average). For example, the attached should have a "Total Average" of 3.30.
The calculation needs to be on a separate sheet as the design of the dashboard that pulls all this together requires it. I tried doing this on the attachment, and I still can only get NULL value for a "Total Average" which causes it to display All.
Any help is greatly appreciated,
PS I like the new avatar
Evaluations Averages.twbx.zip 18.3 KB
I restructured the data to have a number instead of letter on the database level. I think it is cleaner that way, and I have control over the database.
I think the data format requires you to use a manual calculation for average; something like:
Total Average =
(ZN(AVG([question1])) + ZN(AVG([question2])) + ZN(AVG([question3])) + ... )
(IIF(COUNT([question1]) > 0, 1, 0) + IIF(COUNT([question2]) > 0, 1, 0) + IIF(COUNT([question3]) > 0, 1, 0) + ...)
If you reshape the data with one question column and one response column (make it tall instead of wide), you could do something like:
Total Average =
WINDOW_AVG(AVG([Response])) or, in this case the equivalent, TOTAL(AVG([Response]))
The reshaping also makes it easier to work with the data---you only have two pills instead of 13.
One issue with reshaping is that you'll probably want all of the questions in the rows, even if there is no response value. For example, question3 has no responses, but you'd still want this row in your data so that you can, for example, generate a view with each question and a tally of responses, showing null for question3.
Thank you so much for your reply. Through no intelligence on my own part, I stumbled upon this only minutes ago. My formulas are almost identical to yours, except yours is easier to comprehend, and I will be adapting mine to read like yours. (I used a separate calc for the denominator, and somehow window_avg(zn(q1)+zn(q2) etc. for the numerator.)
Thanks for your help,