how to calculate average based on text data against days of weekday

[Maz_0102]

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

[Anonymous]

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.

[Anonymous]

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.

[Maz_0102]

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

[Ashish_Mathur]

Hi,

Based on the dataset that you have shared, show the expected result very clearly.

[Anonymous · 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.