Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
Solved! Go to 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,
Based on the dataset that you have shared, show the expected result very clearly.
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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
86 | |
76 | |
74 | |
56 | |
45 |
User | Count |
---|---|
117 | |
105 | |
77 | |
66 | |
64 |