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

Using a calculated field based on aggregates as a dimension

Steve Field Apr 10, 2017 2:51 PM

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.

Price Group Example.twbx 1.2 MB

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

Deepak Rai Apr 10, 2017 3:00 PM (in response to Steve Field)
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

Price Group Example.twbx 1.2 MB
Like Show 0 Likes(0)

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

Deepak Rai Apr 10, 2017 3:02 PM (in response to Steve Field)

The Grouping field is Groups and is a Dimension.

Thanks
Deepak
Like Show 0 Likes(0)

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

Steve Field Apr 10, 2017 3:09 PM (in response to Deepak Rai)

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
Like Show 0 Likes(0)

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

Deepak Rai Apr 10, 2017 3:24 PM (in response to Steve Field)
Oh, I saw that mistake.I reworked with all values in view and now check.

Price Group Example.twbx 1.2 MB

1 of 1 people found this helpful
Like Show 0 Likes(0)

Actions
5. Re: Using a calculated field based on aggregates as a dimension

Steve Field Apr 11, 2017 8:41 AM (in response to Deepak Rai)

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
Like Show 0 Likes(0)

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

Steve Field Apr 11, 2017 11:12 AM (in response to Steve Field)

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
Like Show 0 Likes(0)

Actions