Download Practice Quizzes
Including step-by-step solutions.
Data Analyst Practice Quizzes
Specialist Practice Quizzes
Online Courses
Our online Tableau courses can help you get certified as a Data Analyst or Tableau Specialist.
Power BI
Interested in learning Power BI? Check out the quizzes here.
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