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

# If-then-else Average not working.

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.

• ###### 1. Re: If-then-else Average not working.

Hi Edmond,

Is this what you're looking for ?

• ###### 2. Re: If-then-else Average not working.

Hi Don,

The average calculation in the screenshot is still off.  For example, let's look at first row (RVD phase, AMFC0047 project). 8.3 is wrong, the correct number should be 8.15.

(7.9 + 7.9 + 8.3 + 7.0 + 8.6 + 9.2) / 6 = 8.15

Thanks.

• ###### 3. Re: If-then-else Average not working.

EDITED/AMENDED

Hi Edmond,

I tried a few things, quite a few things, and didn't hit the actual mark you're looking for (Fri.).  I tried a multiple of LOD's and nested LOD's as well as taking it down to a simple WINDOW_AVG, which should have brought in the right result, but best i could get to was 8.12 and not 8.15 which is what it should be if using the displayed values.

I gave this a go again with a fresher mind this morning (Sat.) and redid some calc's (all can be found as new calc's in Measures) and RVD is the only value that doesn't tie out, the rest do. I can only get 8.12, not 8.15.  Ratio will still be the same: 8.1.  So, can only attribute that to some type rounding issue/algorithm in Tableau, based on the underlying data.  I also wrapped each calc in ZN to account for null values and more appropriately populate the sub/grand totals.  Totals were changed from Automatic to Average BTW.   10.5 workbook newly attached.

Hope it helps!  Best, Don

• ###### 4. Re: If-then-else Average not working.

I did this manually in Excel, and here's what I found.

If you sum all the numerators and divide the result by the sum of all the denominators for all 6 questions for the AMFC Project, RVD Phase, you get an average of 8.285714.

If you average EACH question first, sum those averages and divide the result by 6, you get 8.123016.

If you average each question to ONE decimal place, sum the DISPLAYED single decimal place (so for the first question, 7.9 instead of the actual 7.8571) and divide the result by 6, you get 8.15.

Depending on how you set up the formulas, Tableau is going to give you 8.3 or 8.12; I don't think it's EVER going to give you 8.15.

• ###### 5. Re: If-then-else Average not working.

Thanks Michael...that's what I thought was happening when I wasn't achieving the expected or rather displayed value...I was really wracking myself on this one! I appreciate the validation!!

• ###### 6. Re: If-then-else Average not working.

Hi Guys,

Why do you think Tableau behaves this way?

• ###### 7. Re: If-then-else Average not working.

Hello Edmond,

If reading Michael's response, Tableau calculates based on the resulting value not the displayed value.  The displayed value is formatting only and doesn't change the underlying/actual value. Best, Don

• ###### 8. Re: If-then-else Average not working.

Thank you, Guys.

• ###### 9. Re: If-then-else Average not working.

Hello Edmund,

If you can close the thread by marking a response as correct.  Best, Don