14 Replies Latest reply on Aug 14, 2019 9:33 AM by Ken Flerlage

# Using deviation from monthly avg sales to color code bubble chart

Hello everyone,

I'm trying to compare monthly sales data for each client to the YTD monthly average, defined as SUM(Sales)/Number of months.

I would like to subtract the monthly average to the monthly sales data and use the result to color code a bubble graph that shows monthly sales by client.

So I need a constant figure for the avg monthly sales for each client, so that I can subtract it to the sales of the month I'm analyzing.

As data sample we can use the Superstore data. I don't care what dimension the analysis is performed on, it could be customers or country or whatever.

Thank you very much!

• ###### 1. Re: Using deviation from monthly avg sales to color code bubble chart

Please see if this is what you are looking for

Thanks

1 of 1 people found this helpful
• ###### 2. Re: Using deviation from monthly avg sales to color code bubble chart

So, do you have something like this currently? If so, when you say YTD Monthly Average, do you mean per mark? For instance, would the YTD Monthly Avg for April be SUM(Sales)/4 and the YTD Monthly Avg for December be SUM(Sales)/12 or are you just talking about one static YTD monthly avg for the entire year?

• ###### 3. Re: Using deviation from monthly avg sales to color code bubble chart

If just an overall average for the year shown, then you could create two calculated fields like these:

Monthly Average

// Average monthly sales for the year

{FIXED [Segment] : AVG({FIXED [Segment], YEAR([Order Date]), MONTH([Order Date]): SUM([Sales])})}

Difference

// Difference between the monthly average and the actual monthly sales.

SUM([Sales])-AVG([Monthly Average])

Then drag Difference onto the color card. See attached.

• ###### 4. Re: Using deviation from monthly avg sales to color code bubble chart

Hi Ken,

thank you very much for the answer!

I forgot to mention that in the viz I only have one month. So the graph would have to look something like this: where each bubble represents the sales of a client in the month I'm analyzing in the viz. In your case there would be 3 bubbles in total in the viz.

And the monthly average should be moving so as you showed in your example TD Monthly Avg for April be SUM(Sales)/4 and the YTD Monthly Avg for December be SUM(Sales)/12.

Thanks again!

• ###### 5. Re: Using deviation from monthly avg sales to color code bubble chart

But you're only showing one month at a time? So the above would be April or December, but not both? And each bubble is a separate client?

• ###### 6. Re: Using deviation from monthly avg sales to color code bubble chart

Yes exactly how you just described it!

BUT I just realized that I need to compute the YTD average in a slightly different way. So if I'm showing the data for July in the viz then the YTD average should be SUM(sales)/6 and not 7.

Because I need to compare the sales of the current month to the average of the previous months.

• ###### 7. Re: Using deviation from monthly avg sales to color code bubble chart

I finally had a chance to check your workbook. It's close to what I wanted but if you check the back and forth I had with Ken, you'll see that I only want to show 1 month at a time in my viz.

Also, your YTD average doesn't seem to change as I filter out the months. So for example, if I want to show the bubble chart for July I need the YTD average sales until June (so SUM(sales)/6).

Thanks a lot for your help anyway!

• ###### 8. Re: Using deviation from monthly avg sales to color code bubble chart
1 of 1 people found this helpful
• ###### 9. Re: Using deviation from monthly avg sales to color code bubble chart

OK, so a couple of changes then.

• Remove the pills from Columns and Rows
• Put Segment on the detail card.
• Create a month parameter like this: • Add the Year filter to context. This will ensure that the filter is applied before the FIXED LODs (see Tableau's Order of Operations - Tableau).
• Change Monthly Average to the following: ATTR({FIXED [Segment]: SUM(IIF(MONTH([Order Date])<=[Month], [Sales], NULL))})/[Month]
• Create a new Month Sales calculated field as follows: ATTR({FIXED [Segment]: SUM(IIF(MONTH([Order Date])=[Month], [Sales], NULL))})
• Place Month Sales on the size card.
• Change Difference to the following: [Month Sales]-[Monthly Average]

End result: See attached.

3 of 3 people found this helpful
• ###### 10. Re: Using deviation from monthly avg sales to color code bubble chart

It works! Thank you so much Ken!

When I applied your method to my data initially it wouldn't work. I had to put almost all the filters into context to get the right figures. Could you explain to me why I had to do it? I don't understand the relationship between LODs and the filter context.

• ###### 11. Re: Using deviation from monthly avg sales to color code bubble chart

The key to that question lies in Tableau's Order of Operations (Tableau's Order of Operations - Tableau) Notice that normal "Dimension Filters" are calculated after FIXED LODs. This means that the LODs will calculate before those filters are applied. Thus, they have no effect on the LOD. Sometimes, this is exactly what you want, but other times, you may want your filter to be applied before the FIXED LOD is calculated. In this case, you can make them context filters. Does that make sense?

If I've addressed your question, would you please be so kind as to mark my response as the "correct answer"? This will close the thread and will push that correct answer to the top so people can quickly find the answer to similar questions in the future. Thanks!

1 of 1 people found this helpful
• ###### 12. Re: Using deviation from monthly avg sales to color code bubble chart

HI Voltaire

I have worked on this question using table calculations

1. Add two Parameters : one for "Months" and the other for "Years" 2. All the calculations used in building the chart

3. YTD Average: [ Calculated along Months for each Client and Year]

RUNNING_SUM(SUM([Sales])) / RUNNING_COUNT(COUNT(DATETRUNC('month', [Order Date])))-1

4. Diff Between Monthly Sales and YTD Average:  [ Calculated along Months for each Client and Year]

SUM([Sales])-[YTD Average]

5. The following are added to the filter

[Order Date]<= DATEADD( "day", -1, DATE(STR([Months]) + "-01-"+ STR([Years])))

LAST()=0 [ Calculated along Months for each Client and Year]

Hope this helps

Thanks

1 of 1 people found this helpful
• ###### 13. Re: Using deviation from monthly avg sales to color code bubble chart

Ken Flerlage I'm trying to apply your solution to color code a different visualization that includes and aggregate function and I can't make it work.

Would you be so kind to explain to me what is the reasoning behind your calculated field?

ATTR({FIXED [Segment]: SUM(IIF(MONTH([Order Date])<=[Month], [Sales], NULL))})/[Month]

Why did you wrap everything inside an ATTRIBUTE? What is that SUM for(I underlined it)?

Basically I'm trying to do the same thing but using a measure like Profit ratio, defined as Profit/Sales. Since Profit ratio is a calculated field itself, the IIF function returns the usual error  "Cannot mix aggregate and non-aggregate arguments". The non aggregate argument being the Parameter for the month I guess. I tried to turn it into an aggregate argument but unless I remove the Sum before the IIF it doesn't work. And even if I do it won't return any value.

I can open a new thread if you want.

Thanks again

• ###### 14. Re: Using deviation from monthly avg sales to color code bubble chart

I'd recommend that you read the following to learn a bit more about FIXED LODs