Manually calculating average with missing date values

[dtran]

Hi Community,

I'm trying to manually calculate the average because using AVERAGE funtion is not returning result I want.

What I'm trying to do:

numerator=106%

denominator (should be) 7 days

average (should be) 15% per day <<<<< this is the correct answer that I'm after.

However, as you can see in the screenshot below the AVERAGE function is return 7% which is NOT correct. It's counting the number of days as 15 but there's only data for 7 days.

[v-chuncz-msft · Accepted solution]

@dtran,

You may modify the measures as shown below.

Cnt =
CALCULATE (
    DISTINCTCOUNT ( Table1[date_key] ),
    NOT ( ISBLANK ( Table1[percent] ) )
)

Avg =
DIVIDE ( SUM ( Table1[percent] ), [Cnt] )

[dtran]

Thank you @v-chuncz-msft !

That fixed it.