Forum Discussion

jcastr02's avatar
jcastr02
Icon for Post Prodigy rankPost Prodigy
6 years ago
Solved

Count of Type across multiple columns

I have 5 columns that I split.  The question is "Who explained your roles and responsibilities"   I need to be able to show on visual how many of each one is all 5 columns.  I was thinking a 100% sta...
Need Help
  • dvstrien's avatar
    dvstrien
    6 years ago

    Hi there

    I managed to get something like this using the first few rows of your data. 

    To get this I had to unpivot the table first so I ended up with a table with just two columns; Attribute (col1, col2 col3 etc.) and Value (trainer, my mom, recruiter etc.)

    Next I had to create a measure for each possible value, including blank so you'd need to know every possible answer that people could give. Depending on how the data is collected this may not be possible.

     

    I've added the measures to the value section of the 100% stacked bar visualsation while using the Attribute column for the Axis

     
v-kelly-msft's avatar
v-kelly-msft
Icon for Community Support rankCommunity Support
6 years ago

Hi jcastr02 ,

 

First you need to go to query editor, choose “unpivot columns”:

 

 

Then, you need to create 2 measures as below :

Measure = COUNT('Table'[Value])
Measure 2 = 'Table'[Measure]/CALCULATE(COUNTA('Table'[Value]),ALL('Table'))

 

Then you will see :

 

 

For the related .pbix file,pls click here.

 

Best Regards,

Kelly