Forum Discussion

Snazzybecc's avatar
Snazzybecc
Frequent Visitor
3 years ago
Solved

Calculate % selected answer against total responses

Hi all, having a brain fog day

 

I have a checkbox, multipselect question 

 

If A selected I want to display the number of total rsponses for A as a percentage of the total responses. 

I'm just needing some direction for the structure of the measure 

 

Goal is table

Selecdted option / No responses / % of total responses 

 

So there could be 300 options selecdted but want each row / option to display as a % of the toral responses as opposed to the total no of options selected in the multi select quesiton. 

 

Hope this is clear

 

thnaks

Sam

 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi  Snazzybecc ,

    I created some data:

    Here are the steps you can follow：

    1. Create calculated table.

    Table 2 =
DISTINCT('Table'[Group])

    2. Create measure.

    Measure =
var _select=SELECTCOLUMNS('Table 2',"select",[Group])
return
IF(
    MAX('Table'[Group]) in _select,"Yes responses","No responses")
    select all =
var _select=SELECTCOLUMNS('Table 2',"select",[Group])
var _sum=SUMX(ALL('Table'),[Amount])
var _sumselect=
SUMX(FILTER(ALL('Table'),'Table'[Group] in _select),[Amount])
return
IF(
    MAX('Table'[Group]) in _select,DIVIDE(_sumselect,_sum),BLANK())
    single =
var _select=SELECTCOLUMNS('Table 2',"select",[Group])
var _sum=SUMX(ALL('Table'),[Amount])
return
IF(
    MAX('Table'[Group]) in _select,DIVIDE(MAX('Table'[Amount]),_sum),BLANK())

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

2 Replies

  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    3 years ago

    Snazzybecc 
    May a pattern like the one below ?

    % of Total Response =
DIVIDE (
    COUNTROWS ( VALUES ( 'TABLE1'[Resonse] ) ),
    CALCULATE (
        COUNTROWS ( VALUES ( 'TABLE1'[Resonse] ) ),
        ALLSELECTED ( 'TABLE1'[Resonse] )
    )
)
  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi  Snazzybecc ,

    I created some data:

    Here are the steps you can follow：

    1. Create calculated table.

    Table 2 =
DISTINCT('Table'[Group])

    2. Create measure.

    Measure =
var _select=SELECTCOLUMNS('Table 2',"select",[Group])
return
IF(
    MAX('Table'[Group]) in _select,"Yes responses","No responses")
    select all =
var _select=SELECTCOLUMNS('Table 2',"select",[Group])
var _sum=SUMX(ALL('Table'),[Amount])
var _sumselect=
SUMX(FILTER(ALL('Table'),'Table'[Group] in _select),[Amount])
return
IF(
    MAX('Table'[Group]) in _select,DIVIDE(_sumselect,_sum),BLANK())
    single =
var _select=SELECTCOLUMNS('Table 2',"select",[Group])
var _sum=SUMX(ALL('Table'),[Amount])
return
IF(
    MAX('Table'[Group]) in _select,DIVIDE(MAX('Table'[Amount]),_sum),BLANK())

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