Divide count of a column trough a value of another column

[Nigel92]

Hello, 

I have 2 tables with dates, they are M to 1 linked. The first table has a bunch of serial number that are produced on that day. 

In the second table I have the shift schedule (1/2 shifts).

Out of this I want to find the production count per shift. Is there a simple way to do a count over the serial numbers and divide it between the number that is in the second table [Amount] in the corresponding date? 

[rajendraongole1 · Accepted solution]

Hi @Nigel92 -create a calculated column in the production table to divide the production count by the shift amount for the corresponding date

calculated columns:

ProductionCountPerShift =
VAR CurrentDate = 'ProductionTable'[Date]
VAR ShiftAmount = RELATED('ShiftSchedule'[Amount])
RETURN DIVIDE(CALCULATE(COUNT('ProductionTable'[SerialNumber]), 'ProductionTable'[Date] = CurrentDate), ShiftAmount)

create measure for production count

TotalProductionCount = COUNT('ProductionTable'[SerialNumber])

another calculation to shift 

ProductionCountPerShift =
VAR ShiftAmount = SELECTEDVALUE('ShiftSchedule'[Amount])
RETURN DIVIDE([TotalProductionCount], ShiftAmount)

Hope it works.

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!