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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
electrobrit
Post Patron
Post Patron

Totals of Calendar field "Period Working Days" doesn't sum correctly in total+

First here's a sample pbix

I have a report where I'm trying to get the selection  to add up and show correctly in the totals.
1. For Period Working Hours, I can get it by individual but the total of the period working hours shows for that time period, but does not account for the # of people in selection for whatever time period selected. So if period working hours = 40 x 4people =160 total period working hours in that time period selected. 
Note: in my sample, there are a few people (employee B & D) who no matter how many hours there are in the period selected, their period working hours is equal to how many hours they work thus the change in period working hours. I fixed that by using an if statement and a new "PeriodWorkHrs" field in the calendar table.

The total shows 80 (based on the dax of period working hours (how many hrs not including company holidays in this week) which I would like it to show the actual period working hours (PeriodWorkHrs) column total 299 (not 80)

electrobrit_0-1675368729245.png

My dax for period working hours may need to change up. I'm not sure what I need to do.

Period Working Hours =
var _PeriodEndDate = 'Calendar'[period end]
return CALCULATE(COUNT('Calendar'[Date])*8, FILTER(ALL('Calendar'), 'Calendar'[period end] = _PeriodEndDate && ISBLANK('Calendar'[Holiday])
&& 'Calendar'[Weekday#] in {2,3,4,5,6}))

Thank you!
1 ACCEPTED SOLUTION
v-yueyunzh-msft
Community Support
Community Support

Hi , @electrobrit 

According to your description, the total in your visual is wrong.

For your need , you can create another measure based on the [Period Working Hours] you have created .

You can try to use this dax , you need to change the table name in your side.

Measure=
var _t = ADDCOLUMNS( VALUES('Table'[full_name]),"Period Working Hours",[Period Working Hours])
return
SUMX(_t ,[Period Working Hours])

 

 

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

 

Best Regards,

Aniya Zhang

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

2 REPLIES 2
v-yueyunzh-msft
Community Support
Community Support

Hi , @electrobrit 

According to your description, the total in your visual is wrong.

For your need , you can create another measure based on the [Period Working Hours] you have created .

You can try to use this dax , you need to change the table name in your side.

Measure=
var _t = ADDCOLUMNS( VALUES('Table'[full_name]),"Period Working Hours",[Period Working Hours])
return
SUMX(_t ,[Period Working Hours])

 

 

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

 

 

 

I did provide a sample data too (link at the beginning of the question). However, this worked. Thanks

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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