Calculating Number of Workdays for Each Month for a Year

[Anonymous]

Apologies if this has already been asked, I have looked everywhere and haven't had any luck.

I have two tables, one which contains a row for each shift an employee completes, along with the data of the shift, number of hours worked, and the id number of the working location.

The second table list each employee for each month, for example:

Employee Code,   Employee name,  Month

12345                   John                      January

12345                   John                      February

54321                   Janice                    January

54321                   Janice                    February

What I would like to do against each month/employee is to display the working location where they completed the most hours, I have been trying to use TopN and Rank calculations without much luck. 

I am aware I can use the topn filter, but need this within the table to assist with other aspects of the report.

Any support very welcome.

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Assume you have this dataset

Then you could create measures

total hours = CALCULATE(SUM(Sheet3[hours worked]),ALLEXCEPT(Sheet3,Sheet3[year-month1],Sheet3[employee code],Sheet3[location id]))

rank = RANKX(ALLEXCEPT(Sheet3,Sheet3[year-month1],Sheet3[employee code]),[total hours],,DESC,Dense)

top1 = IF([rank]=1,MAX(Sheet3[location id]),BLANK())

If you have different dataset, please share with me so i can make some transformations on it to meet the final needs.

If you have any problem, please let me know.

Best Regards

Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.