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?