1 Reply Latest reply on Dec 7, 2012 7:39 AM by Mark Holtz

# % of non-NULL values

My data has a lot of measures (15) and a lot of NULL values. I need a simple  way to figure out how many values were entered and how many were null. In the example data set, [] represents NULL values.

ID   Area   M1   M2   M3  M4  M5

1       A      9     2     3     0       9

1       A      9     2     []     0       9

2       B      9     2     3     0       []

2       B      9     []     3     0       9

I do know a lenghty approach. Make a calculated field for each measure - COUNT([Measure]/COUNT(No. of Records)] and then make a graph with all of them as Measure values.

I graph I need is something like this. Nothing complicated - just the percentage of non-NULL values to total values. Any quicker solutions?

• ###### 1. Re: % of non-NULL values

The way your data is structured, I am not aware of any way to get around calculating whether each measure is null or not null.
[M1 NonNull]:
IF NOT(ISNULL([M1])) THEN 1 ELSE 0 END

[M2 NonNull]:
IF NOT(ISNULL([M2])) THEN 1 ELSE 0 END
...

[M15 NonNull]:
IF NOT(ISNULL([M15])) THEN 1 ELSE 0 END

Then you should be able to make your percent as SUM([NonNull Measures]) / SUM(Number of Records) as a calculated field to include on the view.

If you changed the structure of your data so that each unique dimension combination AND measure had its own row, (I call this "unpivoted data") then I believe you could get to it without having to create a field for each separate measure.

You'd simply create a single calculated field of IF NOT(ISNULL([M1])) THEN 1 ELSE 0 END and put your [Measure] on the columns shelf to split the view.

 ID Area Measure Value 1 A M1 9 1 A M2 2 1 A M3 3 1 A M4 0 1 A M5 9 2 B M1 9 2 B M2 2 2 B M3 3 2 B M4 0 2 B M5 [null]
1 of 1 people found this helpful