Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Maz_0102
Helper I
Helper I

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

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

Maz_0102_1-1669123335714.png

 

Maz_0102_2-1669123501990.png

 

 

 

 

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

vpollymsft_0-1669254765657.png

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.

View solution in original post

5 REPLIES 5
v-rongtiep-msft
Community Support
Community Support

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.

v-rongtiep-msft
Community Support
Community Support

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

CardholderDateTimeDay Name
A23/08/202208:30:00Tuesday
A24/08/202208:30:00Wednesday
B25/08/202211:00:00Thursday
A25/08/202208:30:00Thursday
A26/08/202208:30:00Friday
B01/09/202210:30:00Thursday
A07/09/202208:30:00Wednesday
A08/09/202208:30:00Thursday
A09/09/202208:30:00Friday
B12/09/202209:00:00Monday
A13/09/202209:00:00Tuesday
A14/09/202209:00:00Wednesday
A16/09/202208:30:00Friday
A21/09/202208:30:00Wednesday
A27/09/202208:30:00Tuesday
A29/09/202208:30:00Thursday
A03/10/202208:00:00Monday
A10/10/202209:00:00Monday
A11/10/202209:30:00Tuesday
A12/10/202209:30:00Wednesday
A14/10/202209:00:00Friday
A17/10/202209:00:00Monday
A18/10/202209:00:00Tuesday

 

 

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

Maz_0102_0-1669205788042.png

 

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

vpollymsft_0-1669254765657.png

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.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors