Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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.
Solved! Go to Solution.
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:
Proud to be a Super User!
@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 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.
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")
Proud to be a Super User!
@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.
Than please say it before. you can create a extra table with the different goals and use this column in the measure. Let me know if i show it in a example for you.
Proud to be a Super User!
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:
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
95 | |
80 | |
67 | |
62 |
User | Count |
---|---|
147 | |
109 | |
107 | |
85 | |
63 |