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

[khisla]

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%

[v-saisrao-msft]

Hi @khisla,

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

Thank you.

[v-saisrao-msft]

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.

[v-saisrao-msft]

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.

[v-saisrao-msft]

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.

[FBergamaschi · Accepted solution]

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

[v-saisrao-msft]

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.

[khisla]

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

[speedramps]

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 ...

• https://learn.microsoft.com/en-us/training/browse/?products=power-bi
• https://www.youtube.com/watch?v=nn1N0YNx7Uo

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]

[khisla]

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

[lbendlin]

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-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[lbendlin]

use REMOVEFILTERS for that.

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

[Greg_Deckler]

@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

[Greg_Deckler]

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

[khisla]

I am getting the same % for all employees