topic Re: Count of Groups with Average Score Above 6, Possibly GROUPBY Average in DAX Commands and Tips

Count of Groups with Average Score Above 6, Possibly GROUPBY Average

BeeTee — Thu, 11 Aug 2022 22:58:04 GMT

I have a table that has Group, Sales Person, Service Score. I am trying to find a way to calculate the average Service Score by Group and then Count the number of Groups that have an Average Service Score above 6. I have tried to figure out a way to use SUMMARIZE or GROUPBY to make this happen but those functions are new to me and I can't seem to get them to work. Below is an example of the table.

Group	Employee ID	Service Score
Group A	001	5
Group B	002	10
Group C	003	7
Group A	004	3
Group C	005	6

I know I can make a table that shows the average score by group but what I need is the count of Groups with an average score over 6 because my end metric is Percentage of Groups with an average Service Score above 6.

In this example Group A average score = 4, Group B average score = 10, and Group C average score = 6.5 so

Count of Groups with average score above 6 = 2 and percentage of Groups with average scores above 6 = 67% (2/3)

Re: Count of Groups with Average Score Above 6, Possibly GROUPBY Average

daXtreme — Fri, 12 Aug 2022 00:28:53 GMT

DEFINE MEASURE 'Service Scores'[Total Score] = SUM( 'Service Scores'[Service Score] ) MEASURE 'Service Scores'[Average Score by Group] = AVERAGEX( DISTINCT( 'Service Scores'[Group] ), CALCULATE( AVERAGE( 'Service Scores'[Service Score] ), REMOVEFILTERS('Service Scores'[ Employee ID] ) ) ) MEASURE 'Service Scores'[Average Score > 6] = [Average Score by Group] > 6 MEASURE 'Service Scores'[# Groups with Avg Score > 6] = SUMX( DISTINCT('Service Scores'[Group] ), IF( [Average Score > 6], 1 ) )

Re: Count of Groups with Average Score Above 6, Possibly GROUPBY Average

BeeTee — Fri, 12 Aug 2022 14:28:09 GMT

Thanks for your response but I am getting the error "To use special characters in a measure, enclose the entire name in brackets and add a ] to any closing brackets in the name."

Below is what I typed in:

DEFINE MEASURE 'Customer Responses_2022'[TotalScore] = SUM('Customer Responses_2022'[Score]) MEASURE 'Customer Responses_2022'[AvgScore by Group] = AVERAGEX( DISTINCT('Customer Responses_2022'[Group ID]), CALCULATE( AVERAGE('Customer Responses_2022'[Score]), REMOVEFILTERS('Customer Responses_2022'[Employee ID]) ) ) MEASURE 'Customer Responses_2022'[AvgScore 6 or Above] = [AvgScore by Group] >=6 MEASURE 'Customer Responses_2022'[Count of Groups 6 or Above] = SUMX( DISTINCT('Customer Responses_2022'[Group ID]), IF([AvgScore 6 or Above],1) )

Re: Count of Groups with Average Score Above 6, Possibly GROUPBY Average

daXtreme — Sun, 14 Aug 2022 16:33:42 GMT

Hi BeeTee

If your soft says "To use special characters in a measure, enclose the entire name in brackets and add a ] to any closing brackets in the name.", then you should revise your code and look for such unusual characters (remember that some chars are non-printable). The code I gave you works on my station perfectly. I can't help you any more than that since I don't have access to your project. You've got to figure it out by yourself.

Sorry 😞