6 Replies Latest reply on Feb 24, 2016 5:47 AM by Joe Baker

# LOD Weighted Average?

High I'm still wrapping my head around all the different uses of LOD expressions and I could use some assistance with calculating a measure I have for a less granular level of detail.

Below I have a table with two measures

Wgth Avg Sales: SUM([Quantity] * [Sales]) / SUM([Quantity]) ,

LOD Category: {EXCLUDE [Sub-Category] : [Wght Avg Sales (by Quantity)]}

I would like my LOD Category to still show 155.5 even when I filter the Sub-Category as shown, below.  How can I get this to still show the 155.5 when I filter to more granular levels of detail? Should I be using FIXED, and how would I do this?  Thanks!

Using Tableau version 9.2

• ###### 1. Re: LOD Weighted Average?

Could you use LOD Category: {FIXED [Category] : [Wght Avg Sales (by Quantity)]}

And add the year filter to Context

• ###### 2. Re: LOD Weighted Average?

As Dave suggests, use FIXED and add the year filter to context.

The fixed LOD Expression is calculated before the filters are applied (except context filters)

• ###### 3. Re: LOD Weighted Average?

Thanks Guys,

This works for the Sample - Superstore dataset I'm using.

In my actual data set I have my Weighted Avg measure only calculate where the row level has a [Sales] value that is not null. Like this,

Wght Avg : SUM([Sales]*[Quantity]) /

SUM( IF NOT ISNULL([Sales])THEN [Quantity] END)

Do you know I could implement this in the FIXED LOD you guys suggested, so that it calculates only over rows where there is a [Sales] value?

• ###### 4. Re: LOD Weighted Average?

Yes - this should work in either case. You just put it inside the FIXED calculation.

• ###### 5. Re: LOD Weighted Average?

To exclude null values from the LOD expression you could add an other context filter like this

• ###### 6. Re: LOD Weighted Average?

This works, thanks Roland!