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

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

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

        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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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_'s avatar
        Wilson_
        Memorable Member
        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?)