cancel
Showing results for
Did you mean:
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)

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
Community Support

Hi , @electrobrit

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

2 REPLIES 2
Community Support

Hi , @electrobrit

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

Post Patron

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