Forum Discussion
Create a table with buckets
Hi,
I have a table which shows a each day logs when a worker completes a shift. I have a COUNTROWS measure so I can see how many shifts each person has worked in total at the end of the week using a table.
However, I need to create a summary which shows how many people worked 1,2,3,or 4 shifts in that week. I've tried all ways but I'm still not getting any better. Here's a short summary of what I'm trying to achieve (what would be the green table below). Any help is appreciated.
Blue shift effectively is the COUNTROWS total, the orange is the source data and the green is what I'm trying to achieve in PowerBI. Thank you.
Hi All,
Firstly rajendraongole1 and Gabry thank you for your solution!
And phildavies2022 ,I'm happy to tell you that your needs are achievable, here are two ideas I have for you that I hope will help you.
The first method is to carry out two aggregation, in power bi want to achieve the effect of aggregation, we can try to create a new virtual table, and then use the Summarize function, here I carried out two aggregation, and then you can get the effect you want!Summarized Table = SUMMARIZE('employee_data','employee_data'[Employee ID],'employee_data'[People],"Total Number of Shifts",COUNT('employee_data'[Employee ID]))How Many people woked = CALCULATE(COUNT('Summarized Table'[People]),ALLEXCEPT('Summarized Table','Summarized Table'[Total Number of Shifts]))Table = SUMMARIZE('Summarized Table','Summarized Table'[Total Number of Shifts],"HOW MANY PEOPLE WORKED",'Summarized Table'[How Many people woked])The second method is to write the data we need to show on the basis of the source data and perform an aggregation to get your final requirements, which can reduce some unnecessary workload.
Total Number of Shifts(Column) = CALCULATE(COUNTROWS('employee_data'),ALLEXCEPT(employee_data,'employee_data'[Employee ID]))HOW MANY PEOPLE WORKED(2) = CALCULATE(DISTINCTCOUNT('employee_data'[People]),ALLEXCEPT('employee_data','employee_data'[Total Number of Shifts(Column)]))If you still have questions, check out the pbix file I uploaded, I hope my thoughts are helpful and I'd be honoured if I could help you out!
Hope it helps!
Best regards,
Community Support Team_ Tom ShenIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Hello,
pls load sample data or sample pbix, I'll do that
Hi phildavies2022 - create a measure for total shifts as follows
TotalShifts = COUNTROWS('ShiftLogs')
Now create a calculated column for number of shifts on working
ShiftsCategory =
VAR TotalShiftsPerWorker = [TotalShifts]
RETURN
SWITCH(
TRUE(),
TotalShiftsPerWorker = 1, "1 Shift",
TotalShiftsPerWorker = 2, "2 Shifts",
TotalShiftsPerWorker = 3, "3 Shifts",
TotalShiftsPerWorker = 4, "4 Shifts",
"Other"
)using the shiftscategory column ceate a measure to summarize number of workers
WorkersPerShiftCategory =
COUNTROWS(
FILTER(
'YourTable',
'YourTable'[ShiftsCategory] = "1 Shift" ||
'YourTable'[ShiftsCategory] = "2 Shifts" ||
'YourTable'[ShiftsCategory] = "3 Shifts" ||
'YourTable'[ShiftsCategory] = "4 Shifts"
)
)this helps to find the number of workers on each shift.
Hope this helps.
Hi All,
Firstly rajendraongole1 and Gabry thank you for your solution!
And phildavies2022 ,I'm happy to tell you that your needs are achievable, here are two ideas I have for you that I hope will help you.
The first method is to carry out two aggregation, in power bi want to achieve the effect of aggregation, we can try to create a new virtual table, and then use the Summarize function, here I carried out two aggregation, and then you can get the effect you want!Summarized Table = SUMMARIZE('employee_data','employee_data'[Employee ID],'employee_data'[People],"Total Number of Shifts",COUNT('employee_data'[Employee ID]))How Many people woked = CALCULATE(COUNT('Summarized Table'[People]),ALLEXCEPT('Summarized Table','Summarized Table'[Total Number of Shifts]))Table = SUMMARIZE('Summarized Table','Summarized Table'[Total Number of Shifts],"HOW MANY PEOPLE WORKED",'Summarized Table'[How Many people woked])The second method is to write the data we need to show on the basis of the source data and perform an aggregation to get your final requirements, which can reduce some unnecessary workload.
Total Number of Shifts(Column) = CALCULATE(COUNTROWS('employee_data'),ALLEXCEPT(employee_data,'employee_data'[Employee ID]))HOW MANY PEOPLE WORKED(2) = CALCULATE(DISTINCTCOUNT('employee_data'[People]),ALLEXCEPT('employee_data','employee_data'[Total Number of Shifts(Column)]))If you still have questions, check out the pbix file I uploaded, I hope my thoughts are helpful and I'd be honoured if I could help you out!
Hope it helps!
Best regards,
Community Support Team_ Tom ShenIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
