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...
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

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