6 Replies Latest reply on Oct 16, 2018 1:45 PM by Jim Dehner

# calculation is ignoring blanks

I want to calculate AVG Qty of all Months and while calculating its not considering null values.

so here I want grant total(AVG) for first row to be 0.16 (i.e 1/6)

is there any way of doing this?

• ###### 1. Re: calculation is ignoring blanks

hi see the attached superstore example

this fills the nulls with 0

this calculates the average

it returns this

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

1 of 1 people found this helpful

• ###### 3. Re: calculation is ignoring blanks

You can combine Jim and Deepak's approaches in one simple Level of Detail expression that takes the sum for each row and divides by the number of columns in the entire data set.

1 of 1 people found this helpful
• ###### 4. Re: calculation is ignoring blanks

Hello Jim

I did both the calculations.1st formula (fill the null with 0) is working.

however for second it is giving following result.

• ###### 5. Re: calculation is ignoring blanks

Hello Zach,

Thanks formula is working

• ###### 6. Re: calculation is ignoring blanks

see the attached

the test 1 is a nested table calculation - you open the down arrow to get from one to another

the are each set up across

the tet calc below puts the calculation in the Grand Total column - that is the only place where size()=1

all the other fields are the result of the fill null formula

I would have to see your book - it looks like you are trying to use the calculation as a dimension?

it you attach your twbx workbook I will look at it

Jim

1 of 1 people found this helpful