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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors