6 Replies Latest reply on Apr 11, 2017 11:12 AM by Steve Field

# Using a calculated field based on aggregates as a dimension

Hi Everyone,

I'm really stuck and I am starting to think maybe this isn't possible but I'll give it a shot here in case there's a work around...

In my workbook I have a calculated a measure called Average Price which is SUM(Sales)/SUM(Quantity).  I have another Calculation called Price groupings that is If Average Price > 50 then "TOP" Elseif Average Price < 20 then "Bottom" else "Middle" END.

Ideally I'd like to use my Price Groupings as a dimension so that I can summarize Zip codes by Price grouping either by creating a hierarchy or even by subtotals, the problem is that because Average Price is calculated using aggregates (SUM(SALES)/SUM(QUANTITY)  I can't convert it to a dimension... any thoughts on a possible work around.... I've attached a Tableau 10.2 sample packaged workbook using the super store sample DB so you can better see what I'm attempting to do.

• ###### 1. Re: Using a calculated field based on aggregates as a dimension

Hi Steve,

Here it is. Check Screenshot and attached. I used an LOD to do this and Grouped the values of LOD as per your Grouping Calculation.

Hope it Helps!!!

Thanks

Deepak

• ###### 2. Re: Using a calculated field based on aggregates as a dimension

The Grouping field is Groups and is a Dimension.

Thanks

Deepak

• ###### 3. Re: Using a calculated field based on aggregates as a dimension

Well, I had read that using a fixed LOD calculation was a work around but it causes Avg Price to be calculated incorrectly... for example if you look at Postal Code 03060 the Average Price should be \$3.59 but using a LOD calc it's returning as \$7.75

• ###### 4. Re: Using a calculated field based on aggregates as a dimension

Oh, I saw that mistake.I reworked with all values in view and now check.

1 of 1 people found this helpful
• ###### 5. Re: Using a calculated field based on aggregates as a dimension

Deepak!  You are the man!  I tried several variations of the calc using a fixed function and never would have come up with writing it that way but it works!  THANK YOU SO MUCH!!!

Cheers!

Steve

• ###### 6. Re: Using a calculated field based on aggregates as a dimension

The only thing I'd add is that this example it works fine until filters on other dimensions are added.  Once a filter is applied it doesn't calculate correctly... adding the filters to the context fixes this