Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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 |
Solved! Go to Solution.
@SHILL what is a week represent in your data? All days from Sunday to Saterday? Monday to Sunday?
Hi, The week is Monday - Sunday.
@SHILL
Here is a link to download a sample solution file:
Number of updates per week 2022-08-05.pbix
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?
@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:
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.
Hi Sparta, Sorry I cannot download due to restrictions - Could you put in comments again please?
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
33 | |
15 | |
14 | |
13 | |
9 |
User | Count |
---|---|
60 | |
23 | |
22 | |
19 | |
13 |