1 of 1 people found this helpful
Very well crafted question! I like how you provided a sample of what you expect to see, which is very helpful in validating a solution.
I've attached a workbook, with two solutions. It's in version 10.1.3 so you may not be able to open it. I'll describe what I did below:
METHOD 1 — Fixed LOD expression
1. Change [Avg by Category] like so. (Notice I switched to INCLUDE/EXCLUDE expressions, but it can be written as a FIXED expression if you want to stick with FIXED.)
2. Changed [Actual - Avg] like so:
3. Dragged [Avg by Category] back onto the Measure Values shelf to switch it to ATTR(). Voila.
METHOD 2 — Table calculations
1. Change [Avg by Category] as shown. Note the "Default Table Calculation" that was configured ("…computed along Sub-Category").
Hope this helps!