Calculate Average of Multiple Columns Excluding Zero

[Jbro]

 

Hello,

I have survey data with 6 categories - answers are all numerical (1-5)

I am trying to calculate the average of multple columns and exclude any zeros in each column. 

Background:

• The zeros were previously null values that I replaced with zeros
• I did get the average of each individual column using this formula: "CALCULATE(AVERAGE('Survey Results'[Communication]), FILTER('Survey Results', 'Survey Results'[Communication] <> 0"

Issue:

I cannot figure out how to get an average of the data across all 6 columns and filter out for 0

I have created a basic formula for getting the average:

Avg All = (AVERAGE('Survey Results'[Communication])+AVERAGE('Survey Results'[Meetings])+AVERAGE('Survey Results'[Objectives])+AVERAGE('Survey Results'[Schedule])+AVERAGE('Survey Results'[Scope Change Management])+AVERAGE('Survey Results'[Tools]))/6

And I thought I would be able to filter out the zeros doing something like this

Avg All = CALCULATE(AVERAGE('Survey Results'[Communication]), FILTER('Survey Results', 'Survey Results'[Communication] <> 0) + CALCULATE(AVERAGE('Survey Results'[Meetings]), FILTER('Survey Results', 'Survey Results'[Meetings] <> 0) +... /6

But that's not working

Also tried

Avg All = (CALCULATE(AVERAGE('Survey Results'[Communication]), FILTER('Survey Results', 'Survey Results'[Communication] <> 0)) + (CALCULATE(AVERAGE('Survey Results'[Meetings]), FILTER('Survey Results', 'Survey Results'[Meetings] <> 0)) +... /6

and the calculation goes through but it is def not an average as the result is 7.29 rather than in the range of 1-5.  I would expect a in the mid high 4. range.

Any advise?

[tamerj1 · Accepted solution]

Hi @Jbro 

I would rather unpivot the 6 columns using power query. Then it shall not be complicated at all. 

[tamerj1 · Accepted solution]

Hello again @Jbro 
Not sure if I properly explained the solution in the first time. Here is a sample file with the proposed solution https://we.tl/t-qOoLfr8KIu

Average Value = 
AVERAGEX ( 
    FILTER ( 
        'Survey Results',
        'Survey Results'[Value] <> 0
    ),
    'Survey Results'[Value]
)

[Jbro]

@tamerj1Thank you!!

[Jbro]

@tamerj1, thank you for your reply.  To be sure I understand correctly the suggestion is:

Unpivot the 6 columns and calcuate the average using the formula I have above.

But when I do that it nullifies the other graphs I have.  How can I add this as a separate column without touching my curent dataset? 

ETA:  I duplicated the 6 columns and then unpivoted -> created calculation based on the new value field.

Thanks!

[tamerj1]

Hi @Jbro 

I don't know what visuals you have bit you don't need to duplicate the columns. Once unpivoted you will have one value column and one attribute column. You will need to create one measure only and you can sliced by the attributes column. For example you can place date on the rows of a matrix and place the attributes on the columns of the matrix. One measure will be enough and the row and column totals will give you the averages both ways

*Update @Jbro  you measure would be

CALCULATE(AVERAGE('Survey Results'[Communication]), FILTER('Survey Results', 'Survey Results'[Value] <> 0))

[tamerj1 · Accepted solution]

Hi @Jbro 

I would rather unpivot the 6 columns using power query. Then it shall not be complicated at all.