Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Problem calculating production rate

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.

 

 

 

 

 

 

 

 

Need Help
  • Wilson_'s avatar
    Wilson_
    2 years ago

    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?)

9 Replies

  • Wilson_'s avatar
    Wilson_
    Memorable Member
    2 years ago

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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_'s avatar
        Wilson_
        Memorable Member
        2 years ago

        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?)