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

    How to use AVERAGEIFS in tableau?

    Gabriel Mello

      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 timeInfo TypeValue
      09/04/2018 00:2912.154
      09/04/2018 00:3012.261
      09/04/2018 00:3112.295
      09/04/2018 00:3212.258
      09/04/2018 00:3312.269
      09/04/2018 00:3412.341
      09/04/2018 00:3512.360
      09/04/2018 00:3612.388
      09/04/2018 00:3712.363
      09/04/2018 00:3812.345
      09/04/2018 00:3912.401
      09/04/2018 00:4001.752
      09/04/2018 00:4100
      09/04/2018 00:4200
      09/04/2018 00:4300
      09/04/2018 00:4400
      09/04/2018 00:4500
      09/04/2018 00:4600
      10/04/2018 00:2912.326
      10/04/2018 00:3012.444
      10/04/2018 00:3112.483
      10/04/2018 00:3212.445
      10/04/2018 00:3312.459
      10/04/2018 00:3412.540
      10/04/2018 00:3512.562
      10/04/2018 00:3612.595
      10/04/2018 00:3712.570
      10/04/2018 00:3812.553
      10/04/2018 00:3912.616
      10/04/2018 00:4001.911
      10/04/2018 00:4102.360
      10/04/2018 00:4202.388
      10/04/2018 00:4302.363
      10/04/2018 00:4402.345
      10/04/2018 00:4500
      10/04/2018 00:4600
        • 1. Re: How to use AVERAGEIFS in tableau?
          Deepak Rai

          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?
            Branden Kornell

            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?
              Deepak Rai

              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?
                Branden Kornell

                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?
                  Gabriel Mello

                  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 timeNameNumber
                  09/04/2018 00:29Info type1
                  09/04/2018 00:30Info type1
                  09/04/2018 00:31Info type1
                  09/04/2018 00:32Info type1
                  09/04/2018 00:33Info type1
                  09/04/2018 00:34Info type1
                  09/04/2018 00:35Info type1
                  09/04/2018 00:36Info type1
                  09/04/2018 00:37Info type1
                  09/04/2018 00:38Info type1
                  09/04/2018 00:39Info type1
                  09/04/2018 00:40Info type0
                  09/04/2018 00:41Info type0
                  09/04/2018 00:42Info type0
                  09/04/2018 00:43Info type0
                  09/04/2018 00:44Info type0
                  09/04/2018 00:45Info type0
                  09/04/2018 00:46Info type0
                  10/04/2018 00:29Info type1
                  10/04/2018 00:30Info type1
                  10/04/2018 00:31Info type1
                  10/04/2018 00:32Info type1
                  10/04/2018 00:33Info type1
                  10/04/2018 00:34Info type1
                  10/04/2018 00:35Info type1
                  10/04/2018 00:36Info type1
                  10/04/2018 00:37Info type1
                  10/04/2018 00:38Info type1
                  10/04/2018 00:39Info type1
                  10/04/2018 00:40Info type0
                  10/04/2018 00:41Info type0
                  10/04/2018 00:42Info type0
                  10/04/2018 00:43Info type0
                  10/04/2018 00:44Info type0
                  10/04/2018 00:45Info type0
                  10/04/2018 00:46Info type0
                  09/04/2018 00:29value2.154
                  09/04/2018 00:30value2.261
                  09/04/2018 00:31value2.295
                  09/04/2018 00:32value2.258
                  09/04/2018 00:33value2.269
                  09/04/2018 00:34value2.341
                  09/04/2018 00:35value2.360
                  09/04/2018 00:36value2.388
                  09/04/2018 00:37value2.363
                  09/04/2018 00:38value2.345
                  09/04/2018 00:39value2.401
                  09/04/2018 00:40value1.752
                  09/04/2018 00:41value0
                  09/04/2018 00:42value0
                  09/04/2018 00:43value0
                  09/04/2018 00:44value0
                  09/04/2018 00:45value0
                  09/04/2018 00:46value0
                  10/04/2018 00:29value2.326
                  10/04/2018 00:30value2.444
                  10/04/2018 00:31value2.483
                  10/04/2018 00:32value2.445
                  10/04/2018 00:33value2.459
                  10/04/2018 00:34value2.540
                  10/04/2018 00:35value2.562
                  10/04/2018 00:36value2.595
                  10/04/2018 00:37value2.570
                  10/04/2018 00:38value2.553
                  10/04/2018 00:39value2.616
                  10/04/2018 00:40value1.911
                  10/04/2018 00:41value2.360
                  10/04/2018 00:42value2.388
                  10/04/2018 00:43value2.363
                  10/04/2018 00:44value2.345
                  10/04/2018 00:45value0
                  10/04/2018 00:46value0

                   

                   

                   

                   

                   

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

                    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 timeInfo typeValue
                    09/04/2018 00:2912.154
                    09/04/2018 00:3012.261
                    10/04/2018 00:4001.911

                     

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