counting number of shift

[PatrickWong]

I have a table with 3 line and after each work order, the time used is recorded in the table.  I want to count the number of shift used per day based on the hours used. If the usage is more than 12 hours, than another shift is added.

I am able to count the number of shift used per day (in the color column) but

I am unable to count the total number of shift used . it keep returning total of 2 shift used

[parry2k · Accepted solution]

@PatrickWong try following measure, but one thing is not clear in your example, how come # of hrs = 12 is 2 shift, it should be one, no? Jan 2, line 2, 12 hours and you are showing shift = 2

Shift Count1 = 
SUMX ( 
    SUMMARIZE ( 
        hrs,
        hrs[Month],
        hrs[Line]
    ),
    CEILING ( 
        DIVIDE ( 
            CALCULATE( 
                SUM ( 
                    hrs[Hrs] 
                ) 
            ), 
            12 
        ), 
        1 
    )
)

here is the output . Would appreciate Kudos 🙂 if my solution helped.

[PatrickWong]

The expected result should be 14 as total number of shift.  Some of the suggested solution cames backas 13 shift which is not correct.

attached is the file contains the file of my problem

https://1drv.ms/u/s!AtNffqDpLdovao--DhU7cDKIcVg?e=oYceog

[JarroVGIT]

You said in your opening post that another shift should be added when there is more then 12 hours. Do you mean you want a new shift after every 11 hours? (Please see parry2k question, Jan 2nd line 2 has 12 hours, is that 2 shifts or 1?)
If so, then change 12 to 11 in my solution.
Let me know if that works.

[PatrickWong]

Hi Djerro123, 

what I meant was that when the hrs within the shift is between 1-12 hrs, the shift is 1. when the hrs is more than 12 hrs, then the shift becomes 2.

[JarroVGIT · Accepted solution]

@PatrickWong,

Please look at your example again. You state you expect the total to be 14, but there is an ERROR in your reasoning as explained by Parry2K in the first reply.

 

Look at the second line. Then look at your statement when the hrs within the shift is between 1-12 hrs, the shift is 1. That doesn't seem to be correct in your picture of the desired output.

Please explain clearly why the provided solution is incorrect and if so, what statement is incorrect of you (your 12hours=1 statement or your picture in your opening post).

Thanks.

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

[amitchandak]

You need a new shift of 12 too

new 1= CEILING(divide(sum(Sheet1[Hrs]),11.9),1))
Or if want GT to also match

new 1 = 
SUMX(SUMMARIZE(Sheet1,Sheet1[Date],Sheet1[Line],"S1",CEILING(divide(sum(Sheet1[Hrs]),11.9),1)),[S1])

Refer

https://www.dropbox.com/s/ujyf4tcex4a2xux/test%20shift.pbix?dl=0

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[amitchandak]

Try

CEILING(divide(sum(shift[hours]),12))

[JarroVGIT]

Hi @PatrickWong ,

The total of 2 in the Total row is evaluated just like every line above it. It isn't just a summation when the column is a measure. Can you share your DAX of measure [Shifts]? Also, could you share your data in a table format? (that way, we can reproduce your situation easily and figure out a solution for you 🙂 )

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

[JarroVGIT]

You can try this as Shifts measure:

Shifts = 
VAR tmpTable = SUMMARIZE('Table', 'Table'[Date], 'Table'[Line], "Shifts", ROUNDUP(DIVIDE(SUM('Table'[Hours]), 12), 0))
RETURN
SUMX(tmpTable, [Shifts])

results are this:

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

[parry2k · Accepted solution]

@PatrickWong try following measure, but one thing is not clear in your example, how come # of hrs = 12 is 2 shift, it should be one, no? Jan 2, line 2, 12 hours and you are showing shift = 2

Shift Count1 = 
SUMX ( 
    SUMMARIZE ( 
        hrs,
        hrs[Month],
        hrs[Line]
    ),
    CEILING ( 
        DIVIDE ( 
            CALCULATE( 
                SUM ( 
                    hrs[Hrs] 
                ) 
            ), 
            12 
        ), 
        1 
    )
)

here is the output . Would appreciate Kudos 🙂 if my solution helped.