Average counting blank value as 0

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(Metric[REMOVE: 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.