topic Re: Count Measure that is Based off of Other Measures in DAX Commands and Tips

Count Measure that is Based off of Other Measures

Blake753 — Wed, 23 Sep 2020 14:24:00 GMT

I am caculating productivity % and I am trying to build a measure to count them based on certain bins (i.e. how many people have a productivity 80-89%, 90-99% and so on). My Productivity measure is :

[Target Hours Sum]/([Worked Hours Sum]-[Other Productive Hours Sum]

Other Measures that make up that Measure (all names are column names, no measures within measures here):

Target Hours Sum:

SUMX(

SUMMARIZE(

Query1,

Query1[Emp_Name],

Query1[Pay_Period_End],

Query1[TARGETHOURSPAYPERIOD]

)

Worked Hours Sum:

SUMX(

SUMMARIZE(

Query1,

Query1[Emp_Name],

Query1[Pay_Period_End],

Query1[WORKEDHOURSPAYPERIOD]

)

Other Productive Hours Sum:

SUMX(

SUMMARIZE(

Query1,

Query1[Emp_Name],

Query1[Pay_Period_End],

Query1[OTHERPRODUCTIVEHRSPAYPERIOD]

)

What is the best way to go about counting these?

Re: Count Measure that is Based off of Other Measures

Greg_Deckler — Wed, 23 Sep 2020 14:28:05 GMT

Blake753 This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.

Re: Count Measure that is Based off of Other Measures

Blake753 — Wed, 23 Sep 2020 14:40:17 GMT

Greg_Deckler Can you relate that to my specific issue in any way? I'm not quite following what you are saying. Ideally I'm just looking for a "if productivity value is >=0.8 and <0.9 then count how many entries there are", but I'm not sure at all how to do that.

Re: Count Measure that is Based off of Other Measures

Greg_Deckler — Wed, 23 Sep 2020 16:39:49 GMT

Blake753 I can be more specific but I would need to see sample data and expected output.