Forum Discussion
Weekly measures returning blanks!
Hi,
Please try to use this measure to show Average engagement for this week:
Average engagement rate for this week =
AVERAGEX (
SUMMARIZE (
DateKey,
'DateKey'[Date],
"T", AVERAGEX (
FILTER (
ALLSELECTED ( DateKey ),
'DateKey'[Week #] = SELECTEDVALUE ( 'DATA'[WEEK NUM] )
),
[Ave Eng Rate]
)
),
[T]
)
And the result shows:
Here is my test pbix file:
Best Regards,
Giotto Zhi
Hi,
According to your description, please try these measures:
Average engagement rate for last week =
AVERAGEX(SUMMARIZE (
'DateKey','DateKey'[Date],
"T", IF (
SELECTEDVALUE ( 'DateKey'[Week #] ) = 1,
AVERAGEX (
FILTER (
ALL ( 'DateKey' ),
'DateKey'[Week #] = CALCULATE ( MAX ( 'DateKey'[Week #] ), ALL ( 'DateKey' ) )
&& 'DateKey'[Year]
= VALUE ( SELECTEDVALUE ( 'DateKey'[Year] ) ) - 1
),
[Ave Eng Rate]
),
AVERAGEX (
FILTER (
ALL ( 'DateKey' ),
'DateKey'[Week #]
= SELECTEDVALUE ( 'DateKey'[Week #] ) - 1
&& 'DateKey'[Year] = VALUE ( SELECTEDVALUE ( 'DateKey'[Year] ) )
),
[Ave Eng Rate]
)
)
),[T])
Average engagement rate for the week prior to last week =
AVERAGEX(SUMMARIZE (
'DateKey','DateKey'[Date],
"T",
IF (
SELECTEDVALUE ( 'DateKey'[Week #] ) = 1,
AVERAGEX (
FILTER (
ALL ( 'DateKey' ),
'DateKey'[Week #]
= CALCULATE ( MAX ( 'DateKey'[Week #] ) - 1, ALL ( 'DateKey' ) )
&& 'DateKey'[Year]
= VALUE ( SELECTEDVALUE ( 'DateKey'[Year] ) ) - 1
),
[Ave Eng Rate]
),
IF (
SELECTEDVALUE ( 'DateKey'[Week #] ) = 2,
AVERAGEX (
FILTER (
ALL ( 'DateKey' ),
'DateKey'[Week #] = CALCULATE ( MAX ( 'DateKey'[Week #] ), ALL ( 'DateKey' ) )
&& 'DateKey'[Year]
= VALUE ( SELECTEDVALUE ( 'DateKey'[Year] ) ) - 1
),
[Ave Eng Rate]
),
AVERAGEX (
FILTER (
ALL ( 'DateKey' ),
'DateKey'[Week #]
= SELECTEDVALUE ( 'DateKey'[Week #] ) - 2
&& 'DateKey'[Year] = VALUE ( SELECTEDVALUE ( 'DateKey'[Year] ) )
),
[Ave Eng Rate]
)
)
)),[T])
Average weekly engagement rate YTD =
AVERAGEX(SUMMARIZE(DateKey,'DateKey'[Date],"T",TOTALYTD([Ave Eng Rate],DateKey[Date])),[T])
Note that the [Last Calendar Week Engagement] measure is still blank because [WEEK NUM] column in table ‘DATA’ starts from 34 and has no value of 1.
If you want to change it, it is the same with the above:
Last Calendar Week Engagement =
AVERAGEX(SUMMARIZE('DateKey','DateKey'[Date],"T",
CALCULATE(
[Total Engagement],
'DateKey'[Week #] = WEEKNUM(TODAY(),1) -1
)),[T])
And the result shows:
Here is my test pbix file:
Best Regards,
Giotto Zhi
Hi v-gizhi-msft ,
Thank you for your help!
This solves the 'blank' issue, but the numbers aren't looking quite right...
The 'average for last week' measure doesn't match when i check it in a table against the 'eng rate' metric, averaged by week:
https://www.dropbox.com/s/1vlhf94dgeeinvf/Case-Weekly%20measures%20returning%20blanks%21.pbix?dl=0
Thanks again,
Sonia
Hi,
Please try to use this measure to show Average engagement for this week:
Average engagement rate for this week =
AVERAGEX (
SUMMARIZE (
DateKey,
'DateKey'[Date],
"T", AVERAGEX (
FILTER (
ALLSELECTED ( DateKey ),
'DateKey'[Week #] = SELECTEDVALUE ( 'DATA'[WEEK NUM] )
),
[Ave Eng Rate]
)
),
[T]
)
And the result shows:
Here is my test pbix file:
Best Regards,
Giotto Zhi