5 Replies Latest reply on Jan 28, 2019 9:10 AM by swaroop.gantela

    Exclude blank cells from being Included in Average Calculation of a Row

    Lisa Geller

      I have several lists columns of data, most of which contain blank cells.  These cells should remain blank and are purposely left blank.  I cannot seem to figure out how to create a formula that will calculate the average of the cells that only contain values.  Tableau keeps including the blank cells in the calculation, which is artificially lowering my average.  Again - these cells are blank - they are not null cells.

       

       

       

      Here is a screenshot:

       

       

       

      For example - the column LOS Admit has only 22 cells that contain a value.  There are a total of 86 rows.  Tableau keeps calculating the average by summing up the values and then dividing by 86 - I would like it to divide by 22.  I have tried multiple combinations of Window_Count in my denominator but with no success.

       

      I can filter the blank cells out and get the correct answer but would like to try and figure this out without doing that as the filter will alter the averages of my other rows of data.

       

      Thanks

      Lisa

        • 1. Re: Exclude blank cells from being Included in Average Calculation of a Row
          swaroop.gantela

          Lisa,


          Apologies, I'm not able to create a suitable test dataset to simulate the blanks.I tried in Excel and in text file, but couldn't get them to be read in as blanks in Tableau.

          Of what type is your true datasource? I've attached what I tried in the Forum Thread.

           

          Trying to figure out what conditional you can use to detect the blank so that you could

          try a calc like:

           

          WINDOW_AVG(SUM(IF [LOS]<>  //pseudocode for blank//

          THEN [LOS] END))

           

          Are you showing the average as a separate calculated field or in the Grand Total row?

          • 2. Re: Exclude blank cells from being Included in Average Calculation of a Row
            Lisa Geller

            I am attaching a workbook with all of the personal ID stripped away.  The fields I am trying to calculate averages on are calculated fields that use a set.  Maybe that is the issue??

            • 3. Re: Exclude blank cells from being Included in Average Calculation of a Row
              swaroop.gantela

              Lisa,

               

              If I understand you correctly, the currently definition of [LOS DIscharged] is:

              IF [Departed Patients] THEN [LOS] ELSE 0 END

               

              which forces a value of 0 for those patient who are NOT Departed.

              And this is intentional because those 0s are needed in the counting of

              other averages.

               

              For the purposes of calculating the average I created a  calc without the zeroes:

              IF [Departed Patients] THEN [LOS] END

               

              So the trick will be to use the no-zeroes calc for the grand total but the zerosversion for populating the table:

               

              IF MIN([Arrival1])<>MAX([Arrival1])

              THEN AVG([LOS Discharged no Zeros])

              ELSE SUM([LOS Discharged])

              END

               

              This method is described here:

              Customizing Grand Totals – Part 2 | Drawing with Numbers

               

              This does have the unfortunate side effect of changing the color range

              so that the max color is what is on the screen instead of the total dataset.

               

              Please see workbook v10.5 attached in the Forum Thread.

              2 of 2 people found this helpful
              • 4. Re: Exclude blank cells from being Included in Average Calculation of a Row
                Lisa Geller

                Swaroop - thanks so much. 

                 

                The answer was actually incredibly simple but your explanation was what led me to find it.  The problem all along was with my calculated fields for admitted and departed patients - For example - IF [Departed Patients] THEN [LOS] ELSE 0 END.  I don't need that zero there at all - not for any of my other calculations!  I didn't realize those fields contained zeros because the fields are blank when I look at the data in tableau.  I must have some setting turned on that does that?

                 

                  At any rate - I changed the calculation to :IF [Departed Patients] THEN [LOS] END and did the same for my admitted patient field and it all works.  I didn't even need to make a calculated field for the average - tableau does it for me by going to analysis>Totals>calculate all totals using>average and it all works perfectly.

                 

                I'm sorry you went through the trouble to figure out that calculation for me but it was your explanation that made me see exactly what was going on so thank you so much!

                • 5. Re: Exclude blank cells from being Included in Average Calculation of a Row
                  swaroop.gantela

                  Lisa,

                   

                  Glad you came to the resolution.

                  Yes, I hadn't noticed it before, but the default format for the

                  [LOS Discharged] field looks to be hiding the zeros.

                  Please see screenshot below.

                  The second column is with the default setting, that last column is without.