Forum Discussion

mnemolu's avatar
mnemolu
Frequent Visitor
2 years ago
Solved

Cross-tabulation based on multiple-choice questions

We recently conducted a survey featuring multiple-choice questions, allowing participants to select any number of answers for each question.

Our objective is to perform a cross-tabulation based on the collected data in a visually straightforward format. Despite attempting various approaches, achieving this seemingly simple visualization has proven challenging.

The following illustration provides a simplified depiction of our desired outcome. Any assistance in this matter would be greatly appreciated.

 

Sample data:

 

user_idquestionselected
1favorite sportsbasketball
1favorite sportsfootball
2favorite sportsbasketball
3favorite sportsbasketball
3favorite sportsfootball
3favorite sportsswimming
1favorite transportation meanssubway
1favorite transportation meansairplane
2favorite transportation meanssubway
3favorite transportation meanssubway
3favorite transportation meanstrain
3favorite transportation meansairplane
1favorite phone brandsApple
1favorite phone brandsHuawei
2favorite phone brandsSamsung
3favorite phone brandsHuawei
3favorite phone brandsApple
3favorite phone brandsSamsung

 

Need Help
  • Daniel29195's avatar
    Daniel29195
    2 years ago

    mnemolu 

     

    output : 

    example 1 :

     

     

    explanation of how it works : 

    the nb of users displayed =  nb of users that likes apple  and plays basketball .

     

     

    example 2 : 

    explanation of how it works : 

    the nb of users displayed =  nb of users that likes apple and samsung  and plays basketball .

     

     

     

    Measure = 

var answers =  VALUES('Table2'[selected])
var total_answers =  COUNTROWS(answers)

var users =  VALUES(Table2[userid])


var add_col =
ADDCOLUMNS(
    users,
    "@X" ,  CALCULATE(DISTINCTCOUNT(Table2[selected]))
)

var res = 
FILTER(
    add_col,
    [@X]  = total_answers
)

var calc = 
CALCULATE(
    DISTINCTCOUNT(Table2[userid]),
    res
)


RETURN
calc

     

     

     

     

    If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅!

    It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

4 Replies

  • Daniel29195's avatar
    Daniel29195
    Community Champion
    2 years ago

    mnemolu 

     

    output : 

    example 1 :

     

     

    explanation of how it works : 

    the nb of users displayed =  nb of users that likes apple  and plays basketball .

     

     

    example 2 : 

    explanation of how it works : 

    the nb of users displayed =  nb of users that likes apple and samsung  and plays basketball .

     

     

     

    Measure = 

var answers =  VALUES('Table2'[selected])
var total_answers =  COUNTROWS(answers)

var users =  VALUES(Table2[userid])


var add_col =
ADDCOLUMNS(
    users,
    "@X" ,  CALCULATE(DISTINCTCOUNT(Table2[selected]))
)

var res = 
FILTER(
    add_col,
    [@X]  = total_answers
)

var calc = 
CALCULATE(
    DISTINCTCOUNT(Table2[userid]),
    res
)


RETURN
calc

     

     

     

     

    If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅!

    It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

    • mnemolu's avatar
      mnemolu
      Frequent Visitor
      2 years ago

      Thank you very much, I just realized this measure works for cross-tabulation too.

    • mnemolu's avatar
      mnemolu
      Frequent Visitor
      2 years ago

      Thank you so much for your assistance; I've successfully calculated the number of users using the measure you provided. However, I'm still grappling with creating the cross-tabulation visual.

      If the questions were single-choice, it would have been very easy. These multiple-choice questions seem to make the cross-tabulation a lot more challenging.