grouping data for custom category

[Truelearner]

Hi ALL, i have data of an emp login who works for multiple clients , employee logs hours he worked for each client he worked for and i have summaried the total hours employee worked on a day in a column "Hours per day" ,  Now i want to bin the data  like the second table , 0-7 hours as one bin , 7-9 hours as one bin , above 9 hours as one bin , can someone please help  

Emp	client	Date	hours	Hourrs per day
1	A	5/15/2020	1	3.5
1	B	5/15/2020	1.5	3.5
1	C	5/15/2020	1	3.5
1	D	5/16/2020	4	10
1	E	5/16/2020	6	10
2	A	5/15/2020	6	11
2	B	5/15/2020	2	11
2	C	5/15/2020	3	11
2	D	5/16/2020	1	9
2	E	5/16/2020	8	9

Emp1	0 to 7	7 to 9	Above 9
1	1	0	1
2	0	1	1

@v-chuncz-msft @amitchandak @v-wxu@mgwena @cham @cham @amitchandak @Greg_Deckler @Mariusz @Mariusz @yij @yij v-yingj@ v-diye-msft@v-x @v-eachen-msft @v-lionel-msft @v-juanli-msft @v-jayw-msf @v-kelly-msft@v-zhenbw-msft v-yingj@ @harshnathani

[Truelearner]

can someone from community support take a look at it please 

[parry2k]

@Truelearner can you share pbix file with sample data and expected output and I will look at it. My solution answered the original question you posted but seems like there is more than that. 

[parry2k]

@Truelearner add a new column for the bin

Bin = 
SWITCH ( TRUE(),
 Table[Columns] > 9, "More than 9 hours"
 Table[Columns] > 7, "7 - 9",
 "0-7"
)

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[Truelearner]

if i create a column that column will reference the total hours in a day and the count associated with the duplicated depending on the number of line items that employee have on that day . 

[dax]

Hi @Truelearner , 

You could refer to my sample for details.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Truelearner]

The logic you provided will for the sample data but the real data will have multiple days with same hours in those scenarios it will fails ,please check the below data where the logic provided will break ,

bascially i added two rows for employee 1 for for may 17 and the total working hours is same as working hours on 16th may and when i added you logic its failing . Kindly check

EmpclientDatehoursHourrs per dayColumn

1	A	Friday, May 15, 2020	1	3.5	below 7
1	B	Friday, May 15, 2020	1.5	3.5	below 7
1	C	Friday, May 15, 2020	1	3.5	below 7
1	D	Saturday, May 16, 2020	4	10	above 9
1	E	Saturday, May 16, 2020	6	10	above 9
2	A	Friday, May 15, 2020	6	11	above 9
2	B	Friday, May 15, 2020	2	11	above 9
2	C	Friday, May 15, 2020	3	11	above 9
2	D	Saturday, May 16, 2020	1	9	below 9
2	E	Saturday, May 16, 2020	8	9	below 9
1	D	Sunday, May 17, 2020	4	10	above 9
1	E	Sunday, May 17, 2020	6	10	above 9

[dax · Accepted solution]

Hi  @Truelearner ， 

You could try below measure and refer to my sample for details.

Measure 2 = COUNTX(SUMMARIZE('Table (2)','Table (2)'[Emp],'Table (2)'[Date],'Table (2)'[status]),'Table (2)'[status])+0

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.