Problem calculating production rate

[Philippe_Jamon]

Hello everybody,

I have a problem with a dashboard I'm working on.

The data model is like that :

There are four dim tables (on top) and 3 fact tables above.

In the table "Rendement confection", I have sewing operations with date of the operation and ID of the employee (matricule).

I have the duration of each operation in the TempsGamme table.

Each employee can have a different amount of working time per day (the amount of time is in the Matricule table).

I made a calculated column in my fact table to get the amount of time :

MinutesMatricule = if(WEEKDAY(RendementConfection[Créé],2)<>5,LOOKUPVALUE(Matricule[TempsSemaine],Matricule[Matricule],RendementConfection[Matricule]),LOOKUPVALUE(Matricule[TempsVendredi],Matricule[Matricule],RendementConfection[Matricule])) 

I made another calculated columns to get break time of each employee :

TempsArretMatricule = LOOKUPVALUE(TempsArretConfection[TempsArret],TempsArretConfection[Matricule],RendementConfection[Matricule],TempsArretConfection[Date],RendementConfection[Créé]) 

I made another column to show the planned time (which is employee time minus breaks) :

MinutesOuvrées = RendementConfection[MinutesMatricule] - RendementConfection[TempsArretMatricule] 

I want to calculate the production rate of each employee per day, I made a calculated column for that :

Rendement = RendementConfection[Minutes produites] / RendementConfection[MinutesOuvrées] 

When i put the "Rendement" in a matrix, it works for each line but the grand total is false.

I also put the column in a graph and it only shows the grand total so it's not working as expected.

Thanks for your help.

Philippe J.

[Wilson_]

Hi Philippe,

First of all, this should not have been done through calculated columns. In addition, you didn't show the visual pane on your matrix or line chart but I'm guessing both visuals are simply summing up the calculated column, which explains the issue with the grand total.

Can you please share a sample pbix file? (If you don't know how, please check the pinned thread in the forum.) It will make it easier for me to re-write all the calculated columns as measures.

[Philippe_Jamon]

@Wilson_ , Thanks for your answer.

I made a sample pbix and I tried to translate the name of each column so you can understand it easier.

Pbix sample 

I hope this would help.

Thanks for your help.

Philippe.

[Wilson_]

Hi Philippe,

Try this for your production rate measure:

Production Rate (Measure) = 
VAR MinutesProduced = SUM ( Production[MinutesProduced] )
VAR MinutesOpen =
SUMX (
    SUMMARIZE ( Production, Production[EmployeeID], Production[Minutes opened] ),
    Production[Minutes opened]
)
VAR Result =
DIVIDE (
    MinutesProduced,
    MinutesOpen
)

RETURN Result

Instead of adding the percentages together as you are currently doing (ie: dividing the minutes produced by the minutes opened for each employee, then summing), it adds up all the minutes produced and all the minutes opened for all the employees, before doing the division.

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

[Philippe_Jamon]

Hi again @Wilson_ ,

after checking the measure in the pbix file, it seems that it works partially.

The measure is working fine for each day, but it's not working for the total unfiltered and also not working if I select an employee.

It seems that the function that group the minutesopened for each employee doesn't take in consideration the day.

What i need to do is have one iteration of minutesopened per employee and per day (I don't know if it's understandable).

Thanks for your help.

Philippe.

[Wilson_ · Accepted solution]

Philippe,

My mistake, try the below. I just added Production[Date] to the SUMMARIZE function.

Production Rate (Measure) = 
VAR MinutesProduced = SUM ( Production[MinutesProduced] )
VAR MinutesOpen =
SUMX (
    SUMMARIZE ( Production, Production[EmployeeID], Production[Date], Production[Minutes opened] ),
    Production[Minutes opened]
)
VAR Result =
DIVIDE (
    MinutesProduced,
    MinutesOpen
)

RETURN Result

This is what mine looks like now:

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

[Philippe_Jamon]

Thanks, it works perfectly now.

Have a nice day.

Philippe.

[Wilson_]

Awesome, thanks for letting me know. Happy to help, Philippe. 😄

[Philippe_Jamon]

Thanks for your solution @Wilson_ , it seems to work perfectly.

I have a question for my understanding. In my MinutesOpen column, i have the value of a workday, but a row just represent an action during this workday. How the measures doesn't add all the minutesopen (if it was the case, the result would be totally false.

Thanks again for your help and have a nice day.

[Wilson_]

Philippe,

The minutes open is not duplicated in my measure because the SUMMARIZE function I am using groups by the columns in the parameters (in this case, the employee, the date now, and the mninutes opened). You can read more about the function here.