I have occupancy data where it shows the name of staff (cardholder column, text data) against the date and time (rounded column) from the period of 23/08/22 to 24/10/22
from all this data i can create a summary based on the second picture
However, i wanted to show the average of the count of cardholders(staff) against the days throughout that whole period instead of of a summary
is there anyone who can explain me how i can do this please?
i would really appreciate a response from anyone as i am struggling with this one
1 ACCEPTED SOLUTION
Hi @Maz_0102 ,
Please refer to my pbix file.
Create a measure.
average = var _day=CALCULATE(COUNT('Table'[Date]),ALL('Table'))
var _card=CALCULATE(DISTINCTCOUNT('Table'[Cardholder]),ALL('Table'))
return
_day/_card
If I have misunderstood your meaning, please provide more details with your desired output. (a screenshot with your desired result).
How to Get Your Question Answered Quickly
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Maz_0102 ,
Does that make sense? If so, kindly mark my answer as the solution to close the case please. Thanks in advance.
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Maz_0102 ,
What is the data like? Does the count of cardholders(staff) contain duplicate people? The days mean that check-in time for everyone?
Could you please provide some sample data without privacy information and desired output with more details.
How to Get Your Question Answered Quickly
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi
i pasted a small sample size
there are no duplicates but the names appears more than once for different dates
the reason why i a showing day names on the matrix table because i want to compare the days of the week and see which day has the highest peak of entry times from staff
| Cardholder | Date | Time | Day Name |
| A | 23/08/2022 | 08:30:00 | Tuesday |
| A | 24/08/2022 | 08:30:00 | Wednesday |
| B | 25/08/2022 | 11:00:00 | Thursday |
| A | 25/08/2022 | 08:30:00 | Thursday |
| A | 26/08/2022 | 08:30:00 | Friday |
| B | 01/09/2022 | 10:30:00 | Thursday |
| A | 07/09/2022 | 08:30:00 | Wednesday |
| A | 08/09/2022 | 08:30:00 | Thursday |
| A | 09/09/2022 | 08:30:00 | Friday |
| B | 12/09/2022 | 09:00:00 | Monday |
| A | 13/09/2022 | 09:00:00 | Tuesday |
| A | 14/09/2022 | 09:00:00 | Wednesday |
| A | 16/09/2022 | 08:30:00 | Friday |
| A | 21/09/2022 | 08:30:00 | Wednesday |
| A | 27/09/2022 | 08:30:00 | Tuesday |
| A | 29/09/2022 | 08:30:00 | Thursday |
| A | 03/10/2022 | 08:00:00 | Monday |
| A | 10/10/2022 | 09:00:00 | Monday |
| A | 11/10/2022 | 09:30:00 | Tuesday |
| A | 12/10/2022 | 09:30:00 | Wednesday |
| A | 14/10/2022 | 09:00:00 | Friday |
| A | 17/10/2022 | 09:00:00 | Monday |
| A | 18/10/2022 | 09:00:00 | Tuesday |
i have created a separate table for day name so rank the days of the week as well, so there is a relationship between the tables
Hi @Maz_0102 ,
Please refer to my pbix file.
Create a measure.
average = var _day=CALCULATE(COUNT('Table'[Date]),ALL('Table'))
var _card=CALCULATE(DISTINCTCOUNT('Table'[Cardholder]),ALL('Table'))
return
_day/_cardIf I have misunderstood your meaning, please provide more details with your desired output. (a screenshot with your desired result).
How to Get Your Question Answered Quickly
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
