version: 2019.3
First-time poster here... could not find an answer by searching the help documentation or the community, so here goes.
I'm preparing for the Desktop Associate exam and the question asks me to find which subcategory is $31,069 below the average profit across all categories.
I tried some calculations involving AVG and an LOD calc but kept getting the "Cannot mix aggregate and non-aggregate arguments with this function" error.
Then, I tried the below calc with WINDOW_AVG instead of AVG and it worked:
(WINDOW_AVG(sum([Profit]))-sum({fixed [Product Sub-Category]: SUM([Profit])}))
My question: Does WINDOW_AVG in effect use an "absolute" value for AVG? Or did I just fumble my way into an answer, and there's a best-practice type way to do this?
Packaged workbook attached, see sheet "Question 12" for details.
Thanks in advance for helping the noob.
Window_avg and all other window_xx functions are table calculations - they operate on the table that underlays the worksheet (each worksheet is independent -
thy will work top to bottom or left to right at the level and the restart points you select
yours is set like this
had there been more dimensions you would have had to decide how to execute the table calc
another point to remember about table calcs is because they are last in the Order of Operations they can not be used other types of calculations
but they can be nested in additional layers of table calcs
so are they useful - YES and I use them often - I just try to get as far in the analysis as I can before I go there (there is no return path)
