Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
kgamman
Frequent Visitor

Survey responses represented as value?

I am NEW to power bi and I am NOT trained in data analysis, but as part of a call center operations team, I was tasked with creating a dashboard out of data obtained from Survey Monkey.  I am struggling to understand something about how to work with survey responses where numbers represent different rankings.  Our agents were asked to rank their skills on a scale of 1 -5 on well over 100+ products, and when I run the excel spreadsheet from the survey through Power BI, it doesn’t seem to understand what to do with those numbers.  Let’s say for example:

 

Name              Widgets           Wodgets      Gadgets   Bobits

Bob                    5                             1                   1            2

Sally                 2                              5                    5            1

Jim                   1                             4                     1              2

John                   3                             3                  1              5

 

So clearly, Bob knows more about widgets than anyone else, and Sally knows more about Wodgets than anyone else.  We need visualizations where a manager can quickly glance at the dashboard and see this represented in an X/Y graph with the ability to sort by expertise, but the problem I’m having is that Power BI seems to only see the numbers as a “hit”, in other words, if I try to render the data, it basically sees the fact that there’s a number in that field as being a count of something instead of that number actually representing a rank of how much more skilled Bob is at widgets than Jim or Sally, it’s like it’s effectively answering, “yes” to a not yes or no question, if that makes any sense.  I am really wracking my brain on how to get those numbers to ra) associate with one another across 4 groups of datasets and b) represent Expert, Profiecient, Novice, beginner and so forth.  Does anyone have any suggestions, recommendation or advice that might help me transform this type of data into something useable?

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Heya,

If i understand correctly this is what you want right? If so, please tell me and ill share how i did it 🙂 if not, please explain a bit more!

 

edit:

I did some more work to help you out and get started 🙂 Just created some basic charts and filters so you can get familiar with how things work and how to work with your data.
Untitled.png

But,  your data is formatted bad for working with PBI (hence why you didnt get it to work).

Go to Edit Queries > select Widgets Wodgets etc > go to transform and press Unpivot Columns. 

Result should be this:

 

NameAttributeValue
BobWidgets5
BobWodgets1
BobGadgets1
BobBobits2
SallyWidgets2
SallyWodgets5
SallyGadgets5
SallyBobits1
JimWidgets1
JimWodgets4
JimGadgets1
JimBobits2
JohnWidgets3
JohnWodgets3
JohnGadgets1
JohnBobits5

you can ofcourse change attribute and value to whatever you like. 

i added my PBI file here:

https://1drv.ms/u/s!AgnVgd15_ye0hB2B5PWeqoXJX9dz


Feel free to ask any other questions.

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

Heya,

If i understand correctly this is what you want right? If so, please tell me and ill share how i did it 🙂 if not, please explain a bit more!

 

edit:

I did some more work to help you out and get started 🙂 Just created some basic charts and filters so you can get familiar with how things work and how to work with your data.
Untitled.png

But,  your data is formatted bad for working with PBI (hence why you didnt get it to work).

Go to Edit Queries > select Widgets Wodgets etc > go to transform and press Unpivot Columns. 

Result should be this:

 

NameAttributeValue
BobWidgets5
BobWodgets1
BobGadgets1
BobBobits2
SallyWidgets2
SallyWodgets5
SallyGadgets5
SallyBobits1
JimWidgets1
JimWodgets4
JimGadgets1
JimBobits2
JohnWidgets3
JohnWodgets3
JohnGadgets1
JohnBobits5

you can ofcourse change attribute and value to whatever you like. 

i added my PBI file here:

https://1drv.ms/u/s!AgnVgd15_ye0hB2B5PWeqoXJX9dz


Feel free to ask any other questions.

unable to access onedrive please share new link
AB

YES this makes total sense!  I had to delete some unused collumns (email address, merged first name/last name in order to make 1 collumn simply titled "name") in order to make it work, but this simplified and organized my data a great deal! Thank you so much, you really saved the day!!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.