6 Replies Latest reply on Apr 12, 2018 4:31 PM by Branden Kornell

How to use AVERAGEIFS in tableau?

Hello evebody.

I created a table with one datetype dimension and two measure, like the example bellow.

I would like to calculate the average of the column Value when the Info type is iqual 1.

So, if i hide the field date time to show only the day, It need shows

Date time          Average

09/04/2018          2.312

10/04/2018          2.508

Does anybody know how to do this?

I appreciate your help. Thaks so much.

 Date time Info Type Value 09/04/2018 00:29 1 2.154 09/04/2018 00:30 1 2.261 09/04/2018 00:31 1 2.295 09/04/2018 00:32 1 2.258 09/04/2018 00:33 1 2.269 09/04/2018 00:34 1 2.341 09/04/2018 00:35 1 2.360 09/04/2018 00:36 1 2.388 09/04/2018 00:37 1 2.363 09/04/2018 00:38 1 2.345 09/04/2018 00:39 1 2.401 09/04/2018 00:40 0 1.752 09/04/2018 00:41 0 0 09/04/2018 00:42 0 0 09/04/2018 00:43 0 0 09/04/2018 00:44 0 0 09/04/2018 00:45 0 0 09/04/2018 00:46 0 0 10/04/2018 00:29 1 2.326 10/04/2018 00:30 1 2.444 10/04/2018 00:31 1 2.483 10/04/2018 00:32 1 2.445 10/04/2018 00:33 1 2.459 10/04/2018 00:34 1 2.540 10/04/2018 00:35 1 2.562 10/04/2018 00:36 1 2.595 10/04/2018 00:37 1 2.570 10/04/2018 00:38 1 2.553 10/04/2018 00:39 1 2.616 10/04/2018 00:40 0 1.911 10/04/2018 00:41 0 2.360 10/04/2018 00:42 0 2.388 10/04/2018 00:43 0 2.363 10/04/2018 00:44 0 2.345 10/04/2018 00:45 0 0 10/04/2018 00:46 0 0
• 1. Re: How to use AVERAGEIFS in tableau?

Here it Is:

Thanks

Deepak

If it Helps, pl mark it Helpful and CORRECT to Close Thread

• 2. Re: How to use AVERAGEIFS in tableau?

I agree with Deepak's approach, though I wouldn't use a fixed LOD.

I advise creating a second measure, Value2, using his formula: AVG(IF [Info Type] = 1 THEN Value END)

This will work at all levels of detail, and will work if you filter the data.

A second (easier) option is to create a pivot with AVG(Value), and then filter the data to [Info Type] = 1.

• 3. Re: How to use AVERAGEIFS in tableau?

Yes, There are many ways to approach same result. Filtering is not an Issue with LODs, Just add Filter to CONTEXT.

• 4. Re: How to use AVERAGEIFS in tableau?

Agreed; there are usually several solutions in Tableau.

I think the easiest solution, without creating any new calculations at all, is just to create a pivot that looks like this:

• 5. Re: How to use AVERAGEIFS in tableau?

Didnt work here. I dont know why.

May I say that the Info Type and Value are calculated field?

In the original database I have a column with the name and another column with the number, like the example bellow.

Then I created Info type and value calculated fields using:

Info Type: IF CONTAINS ([Name],"Info Type") THEN Number END

Value: IF CONTAINS ([Name],"Value") THEN Number END

 Date time Name Number 09/04/2018 00:29 Info type 1 09/04/2018 00:30 Info type 1 09/04/2018 00:31 Info type 1 09/04/2018 00:32 Info type 1 09/04/2018 00:33 Info type 1 09/04/2018 00:34 Info type 1 09/04/2018 00:35 Info type 1 09/04/2018 00:36 Info type 1 09/04/2018 00:37 Info type 1 09/04/2018 00:38 Info type 1 09/04/2018 00:39 Info type 1 09/04/2018 00:40 Info type 0 09/04/2018 00:41 Info type 0 09/04/2018 00:42 Info type 0 09/04/2018 00:43 Info type 0 09/04/2018 00:44 Info type 0 09/04/2018 00:45 Info type 0 09/04/2018 00:46 Info type 0 10/04/2018 00:29 Info type 1 10/04/2018 00:30 Info type 1 10/04/2018 00:31 Info type 1 10/04/2018 00:32 Info type 1 10/04/2018 00:33 Info type 1 10/04/2018 00:34 Info type 1 10/04/2018 00:35 Info type 1 10/04/2018 00:36 Info type 1 10/04/2018 00:37 Info type 1 10/04/2018 00:38 Info type 1 10/04/2018 00:39 Info type 1 10/04/2018 00:40 Info type 0 10/04/2018 00:41 Info type 0 10/04/2018 00:42 Info type 0 10/04/2018 00:43 Info type 0 10/04/2018 00:44 Info type 0 10/04/2018 00:45 Info type 0 10/04/2018 00:46 Info type 0 09/04/2018 00:29 value 2.154 09/04/2018 00:30 value 2.261 09/04/2018 00:31 value 2.295 09/04/2018 00:32 value 2.258 09/04/2018 00:33 value 2.269 09/04/2018 00:34 value 2.341 09/04/2018 00:35 value 2.360 09/04/2018 00:36 value 2.388 09/04/2018 00:37 value 2.363 09/04/2018 00:38 value 2.345 09/04/2018 00:39 value 2.401 09/04/2018 00:40 value 1.752 09/04/2018 00:41 value 0 09/04/2018 00:42 value 0 09/04/2018 00:43 value 0 09/04/2018 00:44 value 0 09/04/2018 00:45 value 0 09/04/2018 00:46 value 0 10/04/2018 00:29 value 2.326 10/04/2018 00:30 value 2.444 10/04/2018 00:31 value 2.483 10/04/2018 00:32 value 2.445 10/04/2018 00:33 value 2.459 10/04/2018 00:34 value 2.540 10/04/2018 00:35 value 2.562 10/04/2018 00:36 value 2.595 10/04/2018 00:37 value 2.570 10/04/2018 00:38 value 2.553 10/04/2018 00:39 value 2.616 10/04/2018 00:40 value 1.911 10/04/2018 00:41 value 2.360 10/04/2018 00:42 value 2.388 10/04/2018 00:43 value 2.363 10/04/2018 00:44 value 2.345 10/04/2018 00:45 value 0 10/04/2018 00:46 value 0

• 6. Re: How to use AVERAGEIFS in tableau?

This is a case where you need to reshape your data to work with it in Tableau.

[Info type] and [Value] should be two columns on the same row of data. (Right now they are split onto different rows.)

Your data would look like this:

 Date time Info type Value 09/04/2018 00:29 1 2.154 09/04/2018 00:30 1 2.261 10/04/2018 00:40 0 1.911

Then you'll be able to do an Avg([Value]) limited by [Info type].