14 Replies Latest reply on Jan 22, 2013 10:12 AM by Kevin Sullivan

# Avg and Sum depending on dimension

I have a dimension named Waterfall Categories with 13 categories....  within these 13 categories there are 2 that I want be average and the 11 other categories to be sum.

I know this calc does not work but this is what I am looking to do....

If [Waterfall Categories] = "Previous Month" then AVG([Waterfall#]) else If [Waterfall Categories] = "Current Month" then AVG([Waterfall#]) else Sum([Waterfall#]) End

• ###### 1. Re: Avg and Sum depending on dimension

1. Change else if to elseif (all one word)
2. You need to aggregate the dimensions using ATTR()

Something like this:

If ATTR([Category]) = "Previous Month"

then AVG([Profit])

elseIf ATTR([Category]) = "Current Month"

then AVG([Order ID]) else Sum([Profit])

End

--Shawn

• ###### 2. Re: Avg and Sum depending on dimension

I customized your suggestion to meet my needs and its still sum for the 13 categories.  any suggestions?

If attr([Waterfall Categories]) = "Previous Month HC" then Avg([Waterfall#]) elseif attr([Waterfall Categories]) = "Current Month HC" then Avg([Waterfall#]) else sum([Waterfall#]) end

• ###### 3. Re: Avg and Sum depending on dimension

If that's the issue then you'll need something like this:

IF ATTR([Category])="Furniture"

THEN AVG([Profit])

ELSEIF

ATTR([Category])="Office Supplies" OR

ATTR([Category])="Technology" OR

...

THEN SUM([Profit])

END

It ain't pretty but it will work.

--Shawn

• ###### 4. Re: Avg and Sum depending on dimension

This may help.  I have attached my worksheet.  I want Previous Month HC and Current Month HC to be Averages and all other categories to be sum.

• ###### 5. Re: Avg and Sum depending on dimension

Kevin, yep a sample workbook will be very helpful. Unfortunately the workbook you've posted doesn't have any data (twb). You'll need to export a package workbook (twbx) so I can take a look. Thanks,

--Shawn

• ###### 6. Re: Avg and Sum depending on dimension

oops!

• ###### 7. Re: Avg and Sum depending on dimension

Kevin, I'm not sure this can be done, at least not the way you're wanting to display this info. But then I don't do tables. If anyone knows how to do this it will be Jonathan.

--Shawn

• ###### 8. Re: Avg and Sum depending on dimension

Thank you Shawn.  Any suggestions for making it a viz?

• ###### 9. Re: Avg and Sum depending on dimension

Kevin, it's not really good form to compare apples to oranges in a single chart. So instead I'd suggest you create two charts side-by-side. You can do this on a single sheet by using multiple mark types. (See attached.) Here's what it looks like:

--Shawn

• ###### 10. Re: Avg and Sum depending on dimension

Thank you Shawn again for the help!  What I was trying to get to is having the average previous and current headcount on either side of a waterfall chart with the sum of all the other fields in the middle.  the 2 "within" fields would appear but not impact the waterfall.  feeling like this is not possible with my data though.

• ###### 11. Re: Avg and Sum depending on dimension

Hi Kevin,

I built a waterfall chart using the instructions here: http://www.freakalytics.com/2009/11/17/wc/ with the calcs the way you wanted. Note that when you are using calculated fields and aliases, the calculated fields refer to the original values, not the aliases. You can see this in the first worksheet, where the Waterfall with AVG calc is returning 25,756 while the Revised Waterfall # calc is returning 101.

I'm not sure what is going on with the current month headcount, though, the AVG(Waterfall #) is returning 1 for that so the Gantt chart isn't quite accurate.

Jonathan

1 of 1 people found this helpful
• ###### 12. Re: Avg and Sum depending on dimension

Jonathan thank you so much this worked great!  would you be able to guide me in the right direction so on a dashboard I could select a start date and an end date for the waterfall?  So previous month would be the start and current month would be the end date....  I would obviously rename the previous and current month aliases.

Thanks again your example was excellent!

• ###### 13. Re: Avg and Sum depending on dimension

You'rew welcome!

See the attached, I set up two filters, one just a quick filter on the date, the other using two parameters and a calculated field.

• ###### 14. Re: Avg and Sum depending on dimension

This is awesome!  Thank you Jonathan.