1
Which product category has the largest interquartile range for sales?

2
The top 5 customers by sales represent ____ of the total profits.

3
Which product sub-category has total sales which is $81,960 below the average sales per sub-category?

*Hint: First calculate the average sales per subcategory, then subtract this value from the sales broken out by sub-category*

Be sure to click Submit Quiz to see your results!

Thanks, that worked for me. Learnt a new function in the process.

How was Q2 calculated?

To get the average sales per category, you can do total(sales)/countd(sub-categories). If the total sales is 10 million and there are 10 categories, then the average per category is 10/10 or 1 million.

The aggregations still have to be fixed though in order to make them the overall average sales PER all sub-categories instead of average sales IN each sub-category since you need to display the product sub-categories. It’s a slight distinction but it makes a huge difference in the calculation. Alternatively, you could do a WINDOW_AVG on the sum of Sales to get the same result.

Can you provide more elaborate explanation of Question 2. I am stuck in how to calculate this and arrive at an answer

Hi

For question 2, you can use the following steps:

1. calculate AVG sales by sub-category: select sub category and sales, add a ref line to the entire table showing the average sales value, which is 526,584

2. create a calculation field to show the difference : { FIXED [Product Sub-Category]:SUM([Sales])}-526584

3. select product category and the new field to the view and you will see the difference for “office furnishing” is exactly 81960

Hope it helps,

Iris

You can immediately get the average sales by sub-category. Create a bar chart with sub-category and sales, then use analytics to get your average ( average by table which is 526 584). This can be calculated by

Calc Field#1 … avgSalesPerSubCategory

avg({sum([Sales])/COUNTD([Product Sub-Category])})

calcfield#2 … Avg-Sum(sales)

[avgSalesPerSubCategory] – sum([Sales])

Put sales, and both calcs into crosstab and you will see that the second calc gives you 81960 for office furniture.

Need help with Question 1 and 3

Hi

For Question 1, simply select product category and sales, then choose box and whisker plot as the chart form in SHOW ME. Then you will see Furniture has the largest interquartile among three categories.

For Question 3, either sort sales by customer id/name or create a calculation field to find top 5 customers by sales. Press ctrl to select all 5 customers and create a set. select the set and profit, then use quick table calculation to show the % of total. Then you will see ” in 2.63% ”

Hope it helps!

Iris

Not able to understand Q2 calculation?

Hi

For question 2, you can use the following steps:

1. calculate AVG sales by sub-category: select sub category and sales, add a ref line to the entire table showing the average sales value, which is 526,584

2. create a calculation field to show the difference : { FIXED [Product Sub-Category]:SUM([Sales])}-526584

3. select product category and the new field to the view and you will see the difference for “office furnishing” is exactly 81960

Hope it helps,

Iris

Very confused on Q2. I keep getting very low numbers when trying to calculate the AVG sales sub category, as well as the ref line.

Hi Lesley,

You are getting very low numbers when trying to calculate the AVG sales because you most probably are using a formula like AVG(Sales) which will give you the average sales per order. To calculate the average sales by sub-category you must find first the total sum of sales and divide it by the number of subcategories. Then you will subtract the value calculated from each total sales for each category and you will find the correct answer.

Hope this helps,

Narcis

still not able to understand q2

Hi

Could you please be more specific about which part of Question 2 needs more explanation?

Iris

Can someone please explain where we got this formula from?

{ FIXED [Product Sub-Category]:SUM([Sales])}-526584

Hi

I am sorry. May I know which question you are referring to?

Thanks,

Iris

On Q1, no matter how I set it up, I can’t get Tableau to return plots for each of the 3 product categories, using box-and-whisker. It will only return a plot with the quartiles for all three combined. When I try and break it down by each product category, it gives me a single value for each that, from what I can see here, isn’t correct.

Any one else have this problem and find a way to fix it?

Hi Brian,

After you break the view by each product category you must uncheck the aggregate measures option from the Analysis tab. This way you will have the desired view and will be able to answer the question.

Hope this helps,

Narcis