
1. Re: Compare a particular Quarter Sales with the average of other quarters sales.
Stoyko Kostov Aug 11, 2016 11:23 AM (in response to Smeet Patel)Hi Smeet,
You can use a levelofdetail expression (LOD) to help you with that.
Define a calculated field with the following formula: ({FIXED:SUM(Sales)}[Sales])/({FIXED:COUNT(Quarter)}1)
The FIXED LODs instruct Tableau to compute the average ignoring other fields on which your current view may be partitioned.
I also subtract Sales from the SUM, so that the average is computed over all other quarters except the current one. For the same reason I subtract 1 from COUNT.
Then drag Quarter to columns, Sales to Text and the newly created calculated field to Text. This will give you the comparison you need.
I'm attaching a sample workbook that I prepared to demo the above. Let me know if you have other questions.

AverageSalesComparison.twbx 5.3 KB


2. Re: Compare a particular Quarter Sales with the average of other quarters sales.
Smeet Patel Aug 12, 2016 1:28 AM (in response to Stoyko Kostov)https://public.tableau.com/views/AverageSalesQuarterly_Smeet/Sheet1?:embed=y&:display_count=yes
Hi Stoyko,
Thanks for the help.
I am using the "Sample  Superstore Subset (Excel)_0" dataset.
Your solution works fine,but when I tried implementing,it is showing a mismatch in data.
I am attaching the workbook.
I know there is some silly mistake from my end but please help me to figure it out.
Appreciate your efforts in advance.
Quarter of Order Date Year of Order Date Sales Q1 2010 415,880.26 Q2 2010 352,786.14 Q3 2010 456,684.80 Q4 2010 698,981.68 If I select Q1 then the "Average of Sales Except the filtered Quarter" should be (SUM(Q1+Q2+Q3)/3) i.e (1,508,452.62/3)=
502817.54.
But in my worksheet it is showing as 488,156.69.
LOD is:
({FIXED:SUM(Sales)}[Sales])/({FIXED:COUNT([Order Date (Quarters)])}1).
Please let me know if any information is missing.
Thanks and Regards,
Smeet

AverageSalesQuarterly_Smeet.twbx 369.5 KB


3. Re: Compare a particular Quarter Sales with the average of other quarters sales.
Stoyko Kostov Aug 12, 2016 11:05 AM (in response to Smeet Patel)1 of 1 people found this helpfulHi Smeet,
2 things:
1. The way I understood your question, you needed the average of all quarters except the one selected. I.e. if you select Q1, you need (Q2+Q3+Q4)/3. I don't know why you were expecting to see (Q1+Q2+Q3)/3, but if this is indeed what you need, please explain by what criteria you want to exclude Q4 when you select Q1, and I'll try to come up with a formula.
2. I didn't know you had a finer granularity of quarterly sales. Because of that, use the following formula:
({EXCLUDE[Order Date (Quarters)]:SUM(Sales)}{INCLUDE[Order Date (Quarters)]:SUM(Sales)})/({EXCLUDE[Order Date (Quarters)]:COUNTD([Order Date (Quarters)])}1)
EXCLUDE directs Tableau to exclude the specified field from the partitioning, giving you the overall total, and not the total within the current partition.
INCLUDE directs it to include it to the partitioning it already uses, giving you the total within the current partition.
Change your formula to that, and you'll see exactly (Q2+Q3+Q4)/3.
Hope this helps  let me know if you have more questions.