Interesting questions - glad you asked
it is sometimes easier to see what is going on in a text table
the first column is just average sales and uses AVG to total it will return the 229.9 which is the save average as you would get using either the second or the third column
it is the average of all the individual sales figures
the fourth column is
The way you would read an LOD in words would be "for each combination of the dimensions that precede the colon - aggregate based on what follows the colon" --
the LOD is not an aggregate in itself so when you bring it to the viz it is aggregated again
so the fixed avg has no dimension before the colon - so it will give you the average of all the sales in the d/b it is unaffected by the year filter
the exclude LOD - will take into account the year filter but not the category and will give you the average for the individual sales in 2018
the Include LOD is going to use the date filter and only look at 2018 and will compute the average of each category separately - when I brought it to the viz I use average for the total so it divides the sum of the individual averages by 3 (you could use sum and it would just be the total
Note also that the simple average and the exclude average return the same value - the average of all the individual sales in 2018
If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution. Thank you.
averages.twbx 2.4 MB
Thanks Jim, Deepak, both really helpful explanations and examples for working with LOD expressions.
I'm realizing the nut of my question only applies to aggregation with averages, and again I think it's just conceptually around how I can calculate and represent those.
Attached is a workbook showing average total sales per customer, by region. Then the next tab tries to apply the Category dimension, but this of course the re-calculates the average sales on a per category basis, so the overall averages per region increase. I want to keep the totals of the first tab, but find a way to show that Category breakdown.
average+dimension.twbx 1.2 MB