Add 1 to denominator if cell is not null

[Ryanb2412]

Hello Everybody,

I am working with a likert scale question (Strongly Agree = 5 ... Strongly Disagree = 1) for 9 different questions.  The goal is to take the average score for each question asked after I quantify each response.  However, there are some questions that do not have a response.  So, it is possible someone has been surveyed 4 times but one question could only have 2 responses, so I would only want to divide that specific metric question by 2 instead of 4.  These unanswered questions have null in the cells.  Any help on a formula for this would be much appreciated.

Eventually, I will want to sum up all of the responses divided by the times each question was answered to give us a true avg for each employee's performance.

[v-eachen-msft]

Hi @Ryanb2412 ,

You could add a filter to ignore blank values.

FILTER('Table', 'Table'[Value]<> BLANK())

You could provide more information here.

[Greg_Deckler]

Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[Ryanb2412]

@Greg_Deckler 

Sorry, hopefully this is more clear.

Below, I have multiple rep surveys for one rep.  They are scored on 4 metrics below.  I want to find their total avg score, making sure that "null" values are not included in the denominator.

Ex:

Rep	Survey Date	Timely	Trustworthy	Sales	Demos
John Doe	1/1/20	5	5	4	null
John Doe	2/1/20	3	5	null	3
John Doe	3/1/20	4	3	2	null

The desired total avg would be 3.78.  This is calculated by adding all values to = 34 and dividing it by only the 9 cells containing values.  I need to find a measure to create that will omit nulls from any denominator.

[Ryanb2412 · Accepted solution]

Just for anybody seeing this, I used a variable and if statements to find the solution and it has worked correctly so far!

Denominator Count =
VAR Denom = 0

Return

IF(NOT(ISBLANK(Timely)), Denom +1, Denom + 0) + IF(NOT(ISBLANK(Trustworthy)), Denom + 1, Denom + 0) ....