9 Replies Latest reply on Sep 16, 2019 11:34 AM by Don Wise

    If-then-else Average not working.

    Edmond Lee

      Hi Everyone,

      I really need help on my report.  I have attached a copy of the report & screenshot below to this email.

      I’m trying to calculate the average of Project Planning & Requirements (column) for each Phase of the project.  Each project has 4 phases. For each phase, the intended average should be (Q12.1 + Q12.2 + Q12.3 + Q12.4 + Q12.5 + Q12.6) / 6.

      So someone recommended using the formula below to calculate the average. The example comes from the column called Project Planning & Requirements - RVD Avg:

      IF [Phase] = 'RVD'

      THEN

      { FIXED [Project Name],[Phase]:

      AVG(

      [Q12.1 How well is the project planned and tracked?] +

      [Q12.2 How well is the project schedule communicated?] +

      [Q12.3 How do you rate the performance of your Project Manager(s)?] +

      [Q12.4 How do you rate the performance of your Business Analyst(s)?] +

      [Q12.5 How well does your Project Manager communicate and manage the deliverables required from you?] +

      [Q12.6 How effectively do your Project Manager and Technical Lead identify, communicate and manage project dependencies?]

      ) }  / 6

      END

       

      Then I would need to put these 4 averages (columns) into one column called Project Planning & Requirements coz that’s how users want to see.

      For the Total rows, they wanna see average of all 4 phases for each project. The Grand Total row would be average of each project Total.

      Therefore, I came up with the following formula for Project Planning & Requirements:

       

      SUM({ FIXED [Project Name], [Phase]:AVG(

      ZN([Project Planning & Requirements - C&D Avg])

      +

      ZN([Project Planning & Requirements - Production Avg])

      +

      ZN([Project Planning & Requirements - RVD Avg])

      +

      ZN([Project Planning & Requirements - Transition Avg])

      ) } ) /  COUNTD([Phase])

       

      Initially I thought the averages came out correctly, but the formula doesn’t seem to work on this group of fields/columns (Q12.1 – Q12.6).

      Perhaps my formula is wrong.  So can you help me figure out the right formula?  Thank you.

       

      TableauScreenshot.png