Calculate Average and Median when some data is missing

#1

Let's say I have 5 sub-categories under a parent category. I have data with averages and median values for some of those (let's say 3 of the) sub-categories and for rest of the sub-categories, the average and median values are NULL or 0.

Now, I wish to display average and medians for the Parent category. So, I remove the 'Sub-Category' from the rows under data analysis panel. What Dundas BI does is it calculates Average on the basis of the data from the 5 sub-categories (2 of them have 0 or NULL).

I want that the average and the medians for the Parent Category should be calculated only on the basis of the values of the 3 sub-categories. How do I achieve this?


And how can I calculate Median in Dundas BI?

#2

Hi Ravi,


The Average aggregator should automatically exclude null values. If you want to exclude the zero values as well, the best way may be to filter them out or to convert them to null in a data cube.


In order to calculate median for the parent category, you'd want to define a Custom Total Calculation using the MEDIAN function. This function should also exclude nulls from consideration.

#3

Thanks, Davide. Please guide on how do I convert zero values to NULL values in a data cube?

#4

If your measure were called Measure, you could use a Calculated Element transform to create a calculated Element with the following expression:


if($Measure$ == null || $Measure$ == 0){

return null;

}

else{

return $Measure$;

}


Then you could use the calculated Element instead of the original measure.

#5

Thanks, Davide. What I did was extracted fresh data from SQL Query into excel and updated the Data Cube. But, now all the columns in the data set are being identified as 'Dimensions' as almost each column has atleast 1 NULL.