Hello,

how can I calculate the avg of a measure with missing data?

| Measure |

11/01/2022 | 2 |

11/02/2022 | 4 |

11/03/2022 | |

**Sum** | ** 3** |

For 11/03/2022 there is no data, so the avg is calculated with 2 and 4, But I want the missing data used as 0 and the avg schould be 2.

I tried a measure as

coalesce(total [Measure Column] /count (distinct [Date Column]),0)

but I got complete wrong results.

So I tried to calculate each part itself and create a calculated measure but somehow I get the wrong number of dates:

| Dim1.A | | | | Dim1.B | | | | Sum | | | |

| 2022/11/01 | 2022/11/03 | 2022/11/05 | Sum | 2022/11/01 | 2022/11/03 | 2022/11/05 | Sum | 2022/11/01 | 2022/11/03 | 2022/11/05 | Sum |

AVGMeasure (Measue1/Number of Days) | 1.813 | 1.870 | 1.878 | 1.854 | 456 | 450 | 452 | 453 | 1.135 | 1.160 | 1.165 | 1.153 |

Measure (Sum) | 1.813 | 1.870 | 1.878 | 5.561 | 456 | 450 | 452 | 1.358 | 2.269 | 2.320 | 2.330 | 6.919 |

Number of Days | 1 | 1 | 1 | 3 | 1 | 1 | 1 | 3 | 2 | 2 | 2 | 6 |

"Measure (Sum)" is the summarized Measure

"number of Days" is a calculated Measure 'count (distinct [Date])'

However, I didn't find a way for "Number of Days" to not summerize over other Dimensions (2 2 2 6 should be 1 1 1 3)!

When I tried default "number unique values" for [Date] I don't get a value for the Sum.

I'm at my wits end. I believe I can't see the forest for the trees.

Does anyone have an idea what's wrong?