Forum Discussion

djones1855's avatar
djones1855
Frequent Visitor
3 years ago
Solved

Sum Values for specific groups then determine if new value meets goal specific for that group

I have a data table that contains an employee number, week dum, a job family, and a production value.  I need a dax measure that will sum the production for each employee for a given week and then determine if that value is greater than or equal to the goal  or not(Yes = 1 and No = 0).  I was able to create this measure for a different metric before against a single goal using the following:

 

Sums my numerator and denominator for average calculation

24HR_SUM_CONTACT_DAYS = SUM('FIRST CONTACT TRAINING FINAL'[SUM_OF_FIRST_CONTACT_NUM_24])
24HR_SUM_CLMS_CONTACT_DAY = SUM('FIRST CONTACT TRAINING FINAL'[SUM_OF_FIRST_CONTACT_DEN_24])
 
Creates my average
24HR_CONTACT_AVG = DIVIDE([24HR_SUM_CONTACT_DAYS],[24HR_SUM_CLMS_CONTACT_DAY])
 
Tells me what averages have a value greater than .4
24HR_COUNT_CONTACT_AVG = IF('FIRST CONTACT TRAINING FINAL'[24HR_CONTACT_AVG]>=.4,1,0)
 
In my current problem I don't have to deal with an average.  I just need to sum the production for a given week but I need the measure to look at the job family first to determine what value to measure the production sum against.  I tried using a similar formula to the one directly above but it did not allow me to reference job family in the IF statement.

 

Raw data table

 

Goals (not a table currently)

AA >=25

DD >= 20

 

Ultimately if I were to drop this measure into a summary table it would look something like this

I apologize if there is a similar problem to this already on the message boards but I did search for awhile before posting.  I appreciate any help you could provide.

Need Help
  • andhiii079845's avatar
    andhiii079845
    3 years ago

    Hi,

    you want within the group employee, job and week the average? Here is the solution

    Measure = 
    VAR _Table = SUMMARIZE('Table','Table'[Employee],'Table'[Jobfamily],'Table'[Week],"_countWeek",COUNT('Table'[Week]),"_sumweek",sum('Table'[Production]))
    RETURN DIVIDE(sumx(_Table,[_sumweek]),sumx(_Table,[_countWeek]))

     

    Than you can also use if in a second measure 

    MeasureGoal = if([Measure]>4,"Good","Not GOOD")

    Example:

     

6 Replies

  • djones1855's avatar
    djones1855
    Frequent Visitor
    3 years ago

    andhiii079845 i was able to play around with part of the solution you provided in your first reply and got the results I wanted.  Thanks again for your help.

  • andhiii079845's avatar
    andhiii079845
    Icon for Solution Sage rankSolution Sage
    3 years ago

    Hi,

    you want within the group employee, job and week the average? Here is the solution

    Measure = 
    VAR _Table = SUMMARIZE('Table','Table'[Employee],'Table'[Jobfamily],'Table'[Week],"_countWeek",COUNT('Table'[Week]),"_sumweek",sum('Table'[Production]))
    RETURN DIVIDE(sumx(_Table,[_sumweek]),sumx(_Table,[_countWeek]))

     

    Than you can also use if in a second measure 

    MeasureGoal = if([Measure]>4,"Good","Not GOOD")

    Example:

     

  • djones1855's avatar
    djones1855
    Frequent Visitor
    3 years ago

    andhiii079845 thanks for your response.  The summary table snap shot I provided at the end of my post was merely to illustrated what I need the "Meet_Goal" measure to do.  I need it to sum the values grouped by Employee, Job Family and Week, so for week 1, Employee 1A would have a production total of 22, the measure would then see this employee is in Job Family AA which has a goal of 25 so it would then ask, is 22 greater than or equal to 25?  Since it is less then it would output a "0".  The same employee for week 2 has a production total of 26.  Since the job family is still the same so is the goal of 25.  SInce employee 1A's production for week 2 is 26 which is greater than 25 it would output a 1.  Employee 2 is from Job family DD so the measure for this employee would compare it's weekly production totals to the goal for that job family which is 20.  I'm not trying to create a table, just a measure, that outputs either a "1" or a "0" depending on whether their production is at or above gaol or not. 

    • andhiii079845's avatar
      andhiii079845
      Icon for Solution Sage rankSolution Sage
      3 years ago

      You can modify the quality measure like this. 

      MeasureGoal = 
      Var _job = IF(CALCULATE(maxx('Table','Table'[Jobfamily]))="AA",20,25)
      RETURN
      if([Measure]>=_job,"Good","Not GOOD")
      • djones1855's avatar
        djones1855
        Frequent Visitor
        3 years ago

        andhiii079845 Thanks again for contributing. This was a condensed example, so the goals are not just 20 or 25 in the actual data set.  There will be 100's of employees who will fall within 15 different job families in the actual data set and each job family has a different production goal. So an either or measure will no work in this case.