cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

## Number of updates per week

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
1 ACCEPTED SOLUTION
Community Champion

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)

9 REPLIES 9
Community Champion

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

Regular Visitor

Hi, The week  is Monday - Sunday.

Community Champion

@SHILL
Number of updates per week 2022-08-05.pbix

Regular Visitor

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?

Community Champion

@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:

Regular Visitor

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.

Community Champion
Regular Visitor

Community Champion

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)

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.