Forum Discussion

dtran's avatar
dtran
Helper II
9 years ago
Solved

Manually calculating average with missing date values

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.

 

Need Help
  • v-chuncz-msft's avatar
    v-chuncz-msft
    9 years ago

    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] )

2 Replies

  • v-chuncz-msft's avatar
    v-chuncz-msft
    Community Support
    9 years ago

    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] )