Group Average in Stacked Column Chart

[rgu101]

Hello,

I'm trying to create a response distribution of survey results with a stacked column chart by the Question Dimension but not in the traditional sense. My table is similar to this below but with thousands of rows and many different Question Dimensions and Question Texts.

ID	Question Dimension	Question Text	Response
1	Clean/Quiet	Clean	Always
1	Clean/Quiet	Quiet	Sometimes
2	Clean/Quiet	Clean	Never
2	Clean/Quiet	Quiet	Never
3	Clean/Quiet	Clean	Sometimes
3	Clean/Quiet	Quiet	Never

The Question Text response distribution follows the way I want it.

However, the response distribution by Question Dimension is slightly different from the way I need it. It calculates the percentages based count of all IDs within that Question Dimension, however I need to be simply the average of the percentages from Question Text instead of averaging based on the entire Dimension.

For example, the default way is shown below where 55.6% comes from counting all IDs that answered "Always" within the dimension divided by all IDs under Cleanliness/Quietness:

The value I need is simply the average of the Question Texts: (52.9% + 58.4%)/2 = 55.65%

Thanks in advance if there's a solution.

[v-yangliu-msft · Accepted solution]

Hi  @rgu101 ,

Here are the steps you can follow：

1. Create measure.

Measure =
var _countgroup=
COUNTX(
    FILTER(ALL('Table'),'Table'[Question Text]=MAX('Table'[Question Text])&&'Table'[Response]=MAX('Table'[Response])),[Response])
var _count=
COUNTX(
    ALL('Table'),[Response])
var _divide=
DIVIDE(_countgroup,_count)
return
_divide

Measure2 =
var _table1=
SUMMARIZE(
    ALL('Table'),'Table'[Question Text],'Table'[Response],"Value",[Measure])
var _sumx=
SUMX(
    FILTER(_table1,[Response]=MAX('Table'[Response])),[Value])
var _count=
CALCULATE(DISTINCTCOUNT('Table'[Question Text]),FILTER(ALL('Table'),'Table'[Response]=MAX('Table'[Response])))
return
DIVIDE(_sumx,_count)

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[rgu101]

Hello, thanks for the sample code. Unfortunately, the code you suggested doesn't change with my timeline slicer due to the ALL function. I tried modifying it by removing the ALL function, but the result now returns 1 because _countgroup and _count become equal numbers.

[rgu101]

Was able to figure it out by learning about ALLSELECT

QAvg = 
var _countgroup=
COUNTX(
    FILTER(('H ''21-''23'),
    'H ''21-''23'[Question ShortText]=MAX('H ''21-''23'[Question ShortText])
    && 'H ''21-''23'[Response Text]=MAX('H ''21-''23'[Response Text]))
    ,[Response Text]
)

var _count=
CALCULATE(
    COUNTX(ALLSELECTED('H ''21-''23'),
    'H ''21-''23'[Response Text]),
    'H ''21-''23'[Question ShortText]=MAX('H ''21-''23'[Question ShortText])
)

var _divide=
DIVIDE(_countgroup,_count)

return
_divide

DAvg = 
var _table1=
SUMMARIZE(ALLSELECTED('H ''21-''23'),
'H ''21-''23'[Question ShortText],'H ''21-''23'[Response Text],'H ''21-''23'[Discharge Date],
"_QAvg",[QAvg])

var _sumx=
CALCULATE(
SUMX(FILTER(_table1,[Response Text]=MAX('H ''21-''23'[Response Text])),[_QAvg])
)

var _count=
calculate(
   DISTINCTCOUNT('Question Dimensions'[Question Text]),
   FILTER('Question Dimensions',
   'Question Dimensions'[Question Dimension]= MAX('H ''21-''23'[Question Dimension]))
)

return
_sumx/_count

[amitchandak]

@rgu101 , This is like Avg of Avg

Based on the chosen level

AverageX(Values(Table[Question]), calculate(Divide(Sum(Table[Value1]), Sum(Table[Value2]) )) )

Avg of Sum : https://youtu.be/cN8AO3_vmlY?t=22980