7 Replies Latest reply on Aug 30, 2018 5:31 AM by Samuel Mustapha

# How to use AVERAGEIF in Tableau??

First time posting here. Apologize if I am missing the format.

Hypothetically, I have a data set with 3 customers under the "CUSTOMER" dimension, A, B and C. There is "SPENDING" under measurement, within that, A spent \$100, B spent \$101, C spent \$102. I did a simple AVG ([SPENDING]) but now I also want to show the AVG (SPENDING) without customer B. How can I do that?

Thanks.

• ###### 1. Re: How to use AVERAGEIF in Tableau??

hi Jesse,

So one way to do this is to create a Row Level evaluation, and then take the Average of that....

[Spend - Not Customer B]

IIF([Customer]<>"B",[Spend],NULL)

As we have equated the false as NULL (i.e. where customer = B then NULL), it won't get included in any average calculation

You can then bring this onto the canvas, as set the aggregation to AVG

Hope that helps.

• ###### 2. Re: How to use AVERAGEIF in Tableau??

Hi Jesse,

Using the LOD , it is very Straightforward to ignore one Dimension item and Calculate Average of Rest. Check the screenshot and attached.

Thanks

Deepak

• ###### 3. Re: How to use AVERAGEIF in Tableau??

Hi Jesse,

Please use below

{EXCLUDE [Customer]: Sum(If [Customer]<>"B" Then [Spend] END)}/{ EXCLUDE [Customer]: count(if [Customer]<>"B" THEN [Customer] END)}

attaching workbook

• ###### 4. Re: How to use AVERAGEIF in Tableau??

Thanks a lot for your help on this. Can you expand a bit more on the FIX function if you have time?

• ###### 5. Re: How to use AVERAGEIF in Tableau??

Hi Jesse,

I used FIXED LOD here, Which Essentially Says if Customer is not B, then you take the values from A and C and Take their Average and put it across all. The knowledge article is here.

Thanks

Deepak

• ###### 6. Re: How to use AVERAGEIF in Tableau??

hi Jesse,

I had a similar question regarding LoDs recently, so thought you might find it useful...The OP asked me to explain this in SQL (as this is what they are familiar with), but might be a help (including the link to the Quora answer regarding the calculation types in Tableau)

I'd also check these out

• ###### 7. Re: How to use AVERAGEIF in Tableau??

Hi Deepak,

I don't know if you are still monitoring this but I had a question about your solution.

My understanding of the expression "IF [Customer]<>"B" THEN {FIXED:AVG([Spent])} END" is that it creates a bin with the if condition for when customer not B and then assigns the average of spent to every entry. So for the OP's example, there will be two entries for A and C but the average will still be the average of the entire set.

With the OP's data set, the average is the same weather you average the entire set or just the set where customer<>"B", so I changed up the data a bit.

With the new data set, the average without B should be 102.5, but it shows the average to be 233.8. If you do a sum of the Averageif, you get 467.5 which shows that there are two entries of 233.75 (233.8)

I'm new to Tableau so I was hoping to get this clarified as I am currently in the process of understanding nested LODs.