Hi Alexandre,

Someone else may have a better idea but the quickest way to achieve this I can think of is just using IFBLANK([metric],0)

This will replace the blanks with 0’s for the calculation

IFBLANK documentation;

I hope this helps!

Hi Alexandre,

Please be mindful that Pigment remains a sparse engine - this means it only calculates values where there is no blank. For your application to work as fast as possible, you’ll have to try and reduce the number of cells calculated everywhere you can - only calculate / store what makes sense for your business.

While @Keiran’s solution is probably the best if you don’t have a lot of dimensions (the example shown only has 2 dimensions, without a lot of countries), you might want to take additional steps in the case of more complex blocks.

@Keiran’s block shows a block that’s about 95% dense (95% of the cells are filled with values in the original block), which means adding zero’s won’t do much to the speed it takes to calculate your metric.

To find out whether your block is dense or not, you can use the COUNTOF / COUNTALLOF functions:  COUNTOF(Metric) / COUNTALLOF(Metric) will give you an idea of how many zeros you’re adding in the calculation.

In a sparse calculation, you might want to avoid using IFBLANK, using another calculation to fill the zeros in, like this for example:

IF(ISDEFINED(MetricIREMOVE: Month]), IFBLANK(Metric, 0))

This will only fill zeros on countries where you have values (the total on months of each intersection has a value) - I recommend you explore with different modifiers to make your averages work while keeping performance as snappy as possible.

Hello François and Kieran, thank you for your tips ! Indeed, I have many dimensions with lots of blanks.

I’ll try François’ formula 

Hi people,

I’m encountering the same issue, but I’m trying to calculate monthly averages of a transaction list, instead of a metric: so I can’t use IFBLANK or ISDEFINED.

The transaction list has +5 Million rows, so adding 0’s to the transaction list is out of the question.

How can I calculate an average where our loaded data itself is sparse (to save computation power)?

Thanks