topic Re: Calculate percentage of hours worked by each employees per day and per store in DAX Commands and Tips

Calculate percentage of hours worked by each employees per day and per store

khisla — Sat, 26 Jul 2025 13:31:33 GMT

I am looking for a DAX formula to calculate the percentage that each employee worked from the total of all employee for that specific store and day. Please see example data and expected results below:

תאריך	Employee #	Store	Hours Worked	Total Hours Worked	Percentage of total
1/04/2025	2141446691	IL207	7.57	42.25	17.92%
1/04/2025	2114531961	IL207	7.9	42.25	18.70%
1/04/2025	2084683551	IL207	7.72	42.25	18.27%
1/04/2025	2163508841	IL207	7.68	42.25	18.18%
1/04/2025	2163508761	IL207	4.93	42.25	11.67%
1/04/2025	3317250851	IL207	6.45	42.25	15.27%
2/04/2025	2141446692	IL213	8.08	50.11	16.12%
2/04/2025	2114531962	IL213	7.97	50.11	15.91%
2/04/2025	2170302872	IL213	6.47	50.11	12.91%
2/04/2025	2084683552	IL213	8.07	50.11	16.10%
2/04/2025	2142030692	IL213	8.17	50.11	16.30%
2/04/2025	2163508842	IL213	6.03	50.11	12.03%
2/04/2025	3282312202	IL213	5.32	50.11	10.62%
2/04/2025	2141446693	IL224	7.87	47.78	16.47%
2/04/2025	2114531963	IL224	8.12	47.78	16.99%
2/04/2025	3149153233	IL224	6.67	47.78	13.96%
2/04/2025	3275726083	IL224	8	47.78	16.74%
2/04/2025	2163508763	IL224	7.37	47.78	15.42%
2/04/2025	2084683553	IL224	5.53	47.78	11.57%
2/04/2025	3317250853	IL224	4.22	47.78	8.83%
4/04/2025	2141446694	IL135	4.73	39.59	11.95%
4/04/2025	2114531964	IL135	6.58	39.59	16.62%
4/04/2025	3323244094	IL135	6.98	39.59	17.63%
4/04/2025	3282312204	IL135	7.12	39.59	17.98%
4/04/2025	2084683554	IL135	7	39.59	17.68%
4/04/2025	3275726084	IL135	7.18	39.59	18.14%
4/04/2025	3317250855	IL218	2.78	9.21	30.18%
4/04/2025	2163508765	IL218	3.25	9.21	35.29%
4/04/2025	3222369775	IL218	3.18	9.21	34.53%

Re: Calculate percentage of hours worked by each employees per day and per store

Greg_Deckler — Sat, 26 Jul 2025 13:49:39 GMT

khisla Try:

Measure = VAR __Store = MAX( 'Table'[Store] ) VAR __Date = MAX( 'Table'[Date] ) VAR __Hours = MAX( 'Table'[Hours Worked] ) VAR __Table = FILTER( ALL( 'Table' ), [Date] = __Date && [Store] = __Store ) VAR __AllHours = SUMX( __Table, [Hours Worked] ) VAR __Result = DIVIDE( __Hours, __AllHours, 0 ) RETURN __Result

Re: Calculate percentage of hours worked by each employees per day and per store

lbendlin — Sat, 26 Jul 2025 13:55:35 GMT

use REMOVEFILTERS for that.

Store Day contribution = DIVIDE(sum('Table'[Hours Worked]),CALCULATE(sum('Table'[Hours Worked]),REMOVEFILTERS('Table'[Employee #])))

Re: Calculate percentage of hours worked by each employees per day and per store

speedramps — Sat, 26 Jul 2025 14:14:23 GMT

Hi khisla

This is a novice question. 😀😀😀

Please consider doing some basic traning before asking questions here.

You will learn a lot faster and better via a structured learning path rather than asking random questions.

You can learn for free here ...

To get the total of all employees for a store and/or day

you use the CALCULATE and ALL command to override the natural filter total context

Store total = CALCULATE( SUM(yourtable[Hours Worked]), ALL(yourtable[Employee]) )

For example

To get the percentage you divide the employee total by the total for all employees

Employee % = VAR this_eeployee = SUM(yourtable[Hours Worked]) VAR all_employees = CALCULATE( SUM(yourtable[Hours Worked]), ALL(yourtable[Employee]) ) RETURN DIVIDE( this_eeployee, all_employees )

Like this ...

Please click thumbs up and then click [accept solution]

Re: Calculate percentage of hours worked by each employees per day and per store

khisla — Sun, 27 Jul 2025 10:46:03 GMT

Unfortunatley this does not work. I am not getting the total hours worked by store by date.

Re: Calculate percentage of hours worked by each employees per day and per store

khisla — Sun, 27 Jul 2025 10:58:25 GMT

Unfortunately none of the above proposed solutions seem to work. Any other suggestions?

Re: Calculate percentage of hours worked by each employees per day and per store

lbendlin — Sun, 27 Jul 2025 11:18:23 GMT

That sounds like a different requirement.

Please provide sample data that covers your issue or question completely.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: Calculate percentage of hours worked by each employees per day and per store

Greg_Deckler — Sun, 27 Jul 2025 13:33:43 GMT

khisla Calling you attention to this solution as it appears to work perfectly as you can see in these results:

Re: Calculate percentage of hours worked by each employees per day and per store

v-saisrao-msft — Mon, 28 Jul 2025 05:51:47 GMT

Hi khisla,

Please provide the sample data or the PBIX file along with the expected output, instead of just a screenshot, as this will help us better understand and assist you with your issue.

Thank you.

Re: Calculate percentage of hours worked by each employees per day and per store

FBergamaschi — Mon, 28 Jul 2025 09:42:37 GMT

Here is my result

File is here

https://drive.google.com/drive/folders/1ugiXLln6IhhEMVlDx3v_BvqA23CaITVS?usp=sharing

OS I would switch to a dat amodel with a calendar, store and employee dimensions in a star schema but first please tell me if this is what you wanted

If this helped, please consider giving kudos and mark as a solution

me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

Re: Calculate percentage of hours worked by each employees per day and per store

khisla — Thu, 31 Jul 2025 04:13:47 GMT

I am getting the same % for all employees

Re: Calculate percentage of hours worked by each employees per day and per store

v-saisrao-msft — Fri, 01 Aug 2025 07:04:06 GMT

Hi khisla,

I have recreated your scenario in Power BI Desktop with sample data. Please find the attached pbix file along with the DAX measures for your reference.

Hope this helps

Thank you.

Re: Calculate percentage of hours worked by each employees per day and per store

v-saisrao-msft — Mon, 04 Aug 2025 03:40:04 GMT

Hi khisla,

Have you had a chance to review the solution we shared earlier? If the issue persists, feel free to reply so we can help further.

Thank you.

Re: Calculate percentage of hours worked by each employees per day and per store

v-saisrao-msft — Thu, 07 Aug 2025 05:24:16 GMT

Hi khisla,

We haven’t heard back from you in a while regarding your issue. let us know if your issue has been resolved or if you still require support.

Thank you.

Re: Calculate percentage of hours worked by each employees per day and per store

v-saisrao-msft — Fri, 08 Aug 2025 04:12:48 GMT

Hi khisla,

Checking in to see if your issue has been resolved. let us know if you still need any assistance.

Thank you.