2 Replies Latest reply on Dec 29, 2016 2:14 AM by Fábio Rocha

# Newbie - Question similar to countif in excel

Hello guys,

First of all, I want to say I'm new to Tableau, data work and data viz for that matter. My background is IT administration and I'm taking a new challenge in life so please go easy on me

Started three weeks ago and I'm currently picking up some already done work.

I'm banging my head on what I though would be a fairly easy thing.

As an example, I have this data:

 Store Week Sales Store1 Week1 0 Store1 Week1 0 Store1 Week2 0 Store1 Week2 0 Store1 Week3 21 Store1 Week3 9 Store2 Week1 120 Store2 Week2 100 Store2 Week3 50 Store2 Week3 40 Store2 Week3 10 Store3 Week1 15 Store3 Week2 16 Store3 Week3 45

So, in Tableau I created one simple sheet like this:

What I want to do is build a new table with Stores in Rows, one column with SUM(Sales), another column with the COUNT(Week)if SUM(Sales) per week >20, and another column with the AVG(Sales) per Week with Sales>20.

Basically if this was in excel I would countif(b2:d2;">20"), etc to show the number of relevant weeks and then perform a simple average function.

So the result should come something like this:

 Store Sales Relevant Weeks Average Sales per Relevant  Weeks Store1 30 1 30 Store2 320 3 106,7 Store3 76 1 76

Can you help me out on what I need to build to make these calcs?

Cheers.

• ###### 1. Re: Newbie - Question similar to countif in excel

Hi Fábio,

Great question. See attached for a workbook.

Basically, we want to create a FIXED formula to give us the total sales per store per week. We can achieve this by using the formula:

IF {FIXED [Store],[Week]:SUM([Sales])}>20 then 1 else 0 END

Note: The FIXED formula is great to use when you'd like to aggregate data at certain levels/dimensions.

Now that we have those weeks with sales > 20 per store (labeled [Relevant Weeks]), we can create another field using [Relevant Weeks] (formula above) to get the average.

SUM(IF [Relevant Weeks] = 1 THEN [Sales] ELSE 0 END)/SUM([Relevant Weeks])

Hope this helps. Cheers!

1 of 1 people found this helpful
• ###### 2. Re: Newbie - Question similar to countif in excel

Hi Bobby,

Yeah, that worked! Thanks a lot! I managed to get a little more understanding about FIXED as well

Just have an issue regarding this being a LOD expression and having 3/4 filters needed to be applied prior to this calc. Just turned them into context, but I believe this will have an impact on performance as I need to show them on the dashboard so the reader can "customize" the view... I'm gonna wrap this up and compare the difference in performance.

Thanks once again