Forum Discussion

rgu101's avatar
rgu101
Helper I
2 years ago
Solved

Group Average in Stacked Column Chart

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.

IDQuestion DimensionQuestion TextResponse
1Clean/QuietCleanAlways
1Clean/QuietQuietSometimes
2Clean/QuietCleanNever
2Clean/Quiet QuietNever
3Clean/QuietCleanSometimes
3Clean/Quiet QuietNever

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.

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      rgu101
      Helper I
      2 years ago

      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's avatar
        rgu101
        Helper I
        2 years ago

        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