Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Multiple choice question viz

Hello! I'm working on a survey report with a star scheme design, the center being "a00_encuesta_mixed_movements" table. All the other tables are connected through the "uuid" column.

 

In the survey, there is a multiple-choice question in which we ask the population's main needs. We process the responses with an R script that returns the "f06_mainneeds" table, which looks as follows:

 

uuid: is the unique number of each individual
value: is the label of each response category in English
choice: is the Spanish translation of "value"

If a particular individual selects for instance "food_family", a row for that choice will be displayed as you can see in the screenshot. If it was not selected, no row will appear with that uuid.

 

In a bar graph, I need to display the % of people who selected each answer, compared to the total number of respondents. That is to say:

numerator: # of people who selected each of the answer options
denominator: # respondents = COUNT(a00_encuesta_mixed_movements[uuid])

 

I have tried a couple of things:

Tot # per category =
CALCULATE(
    COUNTROWS( f06_mainneeds ),
    ALLEXCEPT(
        f06_mainneeds,
        f06_mainneeds[choice]
    )
)
and then divide this by the total number of surveys, as follows:
% mainneeds by total =
DIVIDE([Tot # per category],[# encuestas])
 
Or directly in the same code:
 
mainneeds % from total =
VAR _sumofchoice =
    CALCULATE ( SUM (f06_mainneeds[choice]), ALLSELECTED( f06_mainneeds) )
VAR _countofuuid =
    CALCULATE ( DISTINCTCOUNT ( f06_mainneeds[uuid]), ALLSELECTED(f06_mainneeds))
RETURN
    DIVIDE ( _sumofchoice, _countofuuid, 0)

 

Although both work, when using any filter in the report, I get % greater than 100%. I think the denominator changes (gets smaller) but the numerator does not. 

 

Any ideas?

Thanks!

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous 

    You can try to change the Tot # per category  measure to the following 

    Tot # per category =
CALCULATE (
    COUNTROWS ( f06_mainneeds ),
    FILTER (
        ALLSELECTED ( f06_mainneeds ),
        [choice] IN VALUES ( f06_mainneeds[choice] )
    )
)

    Best Regards!

    Yolo Zhu

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

     

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous 

    You can try the following code

    mainneeds % from total =
VAR _sumofchoice =
    CALCULATE (
        COUNTROWS ( f06_mainneeds ),
        ALLEXCEPT ( f06_mainneeds, f06_mainneeds[choice] )
    )
VAR _countofuuid =
    CALCULATE ( DISTINCTCOUNT ( f06_mainneeds[uuid] ), ALL ( f06_mainneeds ) )
RETURN
    DIVIDE ( _sumofchoice, _countofuuid, 0 )

    Best Regards!

    Yolo Zhu

    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 Anonymous 

      Thanks for the suggestion but it doesn't work. As you can see in the image, the chart is making some weird calculations and representing the data as if has to add up to 100%, but it doesn't have to. Each of the bars could eventually be 100% themselves. 

      Thanks again. Any other ideas?

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        In this chart the values are the correct ones. 

        To obtain that I've used this solution:

        Tot # per category =
        CALCULATE(
            COUNTROWS( f06_mainneeds ),
            ALLEXCEPT(
                f06_mainneeds,
                f06_mainneeds[choice]
            )
        )
        and then divide this by the total number of surveys, as follows:
        % mainneeds by total =
        DIVIDE([Tot # per category],[# encuestas])
         
        But it doesn't work when using filters, as I described in the original comment.
        Again, any ideas?
        Thanks so much