Number of updates per week

[SHILL]

Hi All, I have the below table and I need to calculate how many times a colleague name appears each week. I could easily see this by setting up visuals however I want a formula so that on some visuals I can have it as an 'average per week' rather then a total.
For example - The below would be that Colleague 1 showed 0 times week 28 , once in week 29 and once in week 30. 
Then if I selected to have a card saying weekly average for that colleague, it would be .66.

Colleague Name	Last Review Date	Case Id
Colleague 2	04/07/2022	#22
Colleague 3	06/07/2022	#54
Colleague 2	06/07/2022	#22
Colleague 2	11/07/2022	#31
Colleague 1	11/07/2022	#89
Colleague 3	19/07/2022	#55
Colleague 2	20/07/2022	#63
Colleague 2	20/07/2022	#22
Colleague 1	20/07/2022	#89
Colleague 3	20/07/2022	#55

[SpartaBI]

@SHILL what is a week represent in your data? All days from Sunday to Saterday? Monday to Sunday?

[SHILL]

Hi, The week  is Monday - Sunday.

[SpartaBI]

@SHILL 
Here is a link to download a sample solution file:
Number of updates per week 2022-08-05.pbix

Showcase Report – Contoso By SpartaBI



      

[SHILL]

Thanks Sparta - Unfortunately due to network restrictions within my workplace I am unable to download this - Is there a measure you can put in the comments?

[SpartaBI]

@SHILL sure. This is the measure:

Appearance Count = 
VAR _appearance = COUNTROWS('Table')
VAR _total_weeks = CALCULATE(DISTINCTCOUNT('Table'[Week Num]), REMOVEFILTERS())
RETURN
_appearance / _total_weeks

And this is how it looks in the file:

Showcase Report – Contoso By SpartaBI



      

[SHILL]

Thank you - Its not quiet doing what I want however. I want it to show that 
Colleague 1 'appeared' in week 28 0 times, Week 29 once, Week 30 once. 

[SpartaBI]

@SHILL check now please:
Number of updates per week 2022-08-05.pbix

Showcase Report – Contoso By SpartaBI



      

[SHILL]

Hi Sparta, Sorry I cannot download due to restrictions - Could you put in comments again please?

[SpartaBI · Accepted solution]

@SHILL 

Appearance Count = COUNTROWS('Table') + 0

Appearance Count Pct =

VAR _appearance = [Appearance Count]

VAR _total_weeks = CALCULATE(DISTINCTCOUNT('Table'[Week Num]), REMOVEFILTERS())

RETURN

_appearance / _total_weeks

Appearance for any colleague test =

INT(

    CALCULATE(

        NOT ISEMPTY('Table'),

        REMOVEFILTERS('Table'[Colleague Name],'Table'[Case Id])

    )

)

Week Num = WEEKNUM('Table'[Last Review Date], 2)

Showcase Report – Contoso By SpartaBI