
1. Re: LOD vs Table Calculation
Deepak Rai Nov 12, 2018 10:03 AM (in response to Larsen Rennie){AVG({ Fixed [Sub Category]:MEDIAN([Sales])})}

2. Re: LOD vs Table Calculation
Larsen Rennie Nov 12, 2018 10:10 AM (in response to Deepak Rai)Hi Deepak
i tried with above expression which you mentioned but its giving me 206 which is incorrect.

3. Re: LOD vs Table Calculation
Deepak Rai Nov 12, 2018 10:11 AM (in response to Larsen Rennie)ok let me recreate something

4. Re: LOD vs Table Calculation
Deepak Rai Nov 12, 2018 10:33 AM (in response to Deepak Rai)1 of 1 people found this helpful 
5. Re: LOD vs Table Calculation
Jonathan Drummey Nov 12, 2018 3:22 PM (in response to Larsen Rennie)Hi Larsen,
When you're working with Superstore data please be sure to attach the packaged workbook, that helps us make sure we're using the same version as you. The behavior that you're seeing in the AVG of the FIXED LOD is no longer current behavior for Tableau (there were edge cases when LODs were introduced in v9.0 that were slowly fixed over later versions).
In any case what the AVG([FIXED LOD]) will do is compute the average *in the current context* which given the vizLOD of Category & SubCategory means that the average of the fixed LOD will be the average for each SubCategory. Here's a view built with v9.3:
Now here's what happens when I remove SubCategory from the vizLOD (I dropped the Window Avg table calc because it won't work in this view, since SubCategory is no longer in the vizLOD but Category is the average of the FIXED calc is the correct average of the subcategory level median sales:
There's an extra calculation that I added to both views, that's the Exc/Inc Avg of Median Sales, here's the formula:
{EXCLUDE [SubCategory]: AVG({INCLUDE [SubCategory] : MEDIAN([Sales])})}
The inner INCLUDE is getting the median sales per subcategory, then the outer EXCLUDE computes the average across subcategories (by excluding the SubCategory dimension), and Tableau adds the default ATTR() aggregation for EXCLUDE LOD expressions.
This calculation is the only one that works at either vizLOD, the other calcs will work at one vizLOD but not the other.
Note that we could write this using FIXED LOD expressions with the following formula:
{FIXED [Category] ; AVG({FIXED [SubCategory]: MEDIAN([Sales])})}
This formulation has two limitations: First of all it's not responsive to other dimensions in the view. For example if we brought year as a dimension into the view then we'd have to edit the calculation. Secondly if we want filters to affect the fixed results then we have to remember to add them as context filters. The Exc/Inc version has neither limitation, so it's my preferred method if I want do replicate a WINDOW_ function in an LOD expression.
v9.3 workbook is attached.
Jonathan

window avg in lod.twbx 1.3 MB


6. Re: LOD vs Table Calculation
Larsen Rennie Nov 12, 2018 10:39 PM (in response to Deepak Rai)Hi Deepak
The solution which you provided worked for me as well as Jonathon Solution . Thank you to both of you for helping me out with pulling this calculation. I really appreciate Jonathon explanation on how we can write the efficient solution for lod without altering the view and without the use of context filter
Thank you all

7. Re: LOD vs Table Calculation
Larsen Rennie Nov 12, 2018 11:03 PM (in response to Jonathan Drummey)Hi Jonathon
The solution which you provided is amazing . I have one doubt which i want to clear from you. Lets say i have created a group out of the Category column because i want to group certain categories which is the scenario with my real project and then if i use that group instead of Category on the row shelf will the calculation u wrote above be applicable for that also or do i need to do anything else. If you can pull an example of this scenario using the Group that will be great
Thanks
Larsen