Solved: Multiple question ratings in one visual (scale 1 t...

kevcurtis · ‎12-01-2017

Hello Power BI Community,

I'm attempting to migrate an existing Tableau (screen shot bleow) report to Power BI. I've had difficulity repsenting the data in the same way Tableau does and I can't seem to find a one-to-one solution. The objective is to visually represent survey reponse ratings on a scale of 1 to 5 by section.

Each survey section has 4 questions with a text response that is mapped to a numerical value. I'd like to plot the numerical values for each section with 4 unqiue shapes based on a scale of 1 to 5. If the values for a two or more questions are the same, the shapes can be transparent so you can see if the ratings overlap.

X = Answer_Numerical (or answer_text)

Y = Section_Name

Any suggestions would be appreciated. I've pasted a sample set of data for reference.

Thanks

Kevin

Approaches I've tried...

Approach 1:

Creating a matrix with the rating categories (Rating current, Rating desired, External current, External desired) as values and then using measures to group them

MultCategory = CONCATENATEX(VALUES('Survey 123'[Category]),Category, ",")

and then display a symbol:

3MStatusIcons = 
VAR RC = UNICHAR (9675)
VAR RD = UNICHAR (9679)
VAR EC = UNICHAR (9671)
VAR ED = UNICHAR (9670)
VAR RCRD = UNICHAR (9673)
VAR ECED = UNICHAR (9672)
RETURN
IF([MultCategory] = "Rating current", RC,
IF([MultCategory] = "Rating desired", RD,
IF([MultCategory] = "External current", EC,
IF([MultCategory] = "External desired", ED,
IF([MultCategory] = "Rating current,Rating desired", RCRD,
IF([MultCategory] = "External current,External desired", ECED,""))))))

This approach allows for symbols to the displayed in the matrix visual but there are a large number of combinations that would make a legend unmanagable and difficult to consume the information. (see: http://community.powerbi.com/t5/Desktop/Scatter-plot-with-non-aggregated-data-Display-current-and/m-...)

Approach 2:

Pivot the Category column with Answer_Numerical as the value for each. Created MIN measures for each category and attempted to use a scatter plot with no luck.

Tableau report I'd like to replicate:

(sample only displays Rating current & Rating desire values)

Assessment ratings.png

Sample data:

User	Id_Question	Section_Name	Answer_Text	Answer_Numerical	Category
1	12254	Section 1	Rating 3.5	3.5	Rating current
1	12255	Section 1	Rating 5	5	Rating desired
1	12252	Section 1	Rating 2.5	2.5	External current
1	12253	Section 1	Rating 3.5	3.5	External desired
1	12262	Section 2	Rating 3.5	3.5	Rating current
1	12263	Section 2	Rating 4	4	Rating desired
1	12265	Section 2	Rating 2.5	2.5	External current
1	12266	Section 2	Rating 3	3	External desired
1	12274	Section 3	Rating 3.5	3.5	Rating current
1	12273	Section 3	Rating 4	4	Rating desired
1	12275	Section 3	Rating 4.5	4.5	External current

stretcharm · ‎12-04-2017

No problem.

There are other visuals that might be able to do what you need.

e.g.

https://appsource.microsoft.com/en-us/product/power-bi-visuals/WA104380980?tab=Overview

https://appsource.microsoft.com/en-us/product/power-bi-visuals/WA104381299?tab=Overview

You can also try R it can do almost any chart

https://docs.microsoft.com/en-us/power-bi/desktop-r-visuals

http://r-statistics.co/Top50-Ggplot2-Visualizations-MasterList-R-Code.html

View solution in original post

stretcharm · ‎12-03-2017

Have you tried a Bullet Chart?

I pivoted your data and added a bullet chart and it doesn't look the same but can show the same data.

https://okviz.com/bullet-chart/

http://blog.pragmaticworks.com/power-bi-custom-visuals-bullet-chart

kevcurtis · ‎12-04-2017

Hi @stretcharm,

Thank you for the suggestion.

The Bullet Chart custom visual is a step in the right direction in terms of the data representation for two ratings. When more than two ratings are displayed it starts to get busy with the combination of bars and shapes.

I will work more with the visual today to see if I can get it to meet my needs.

Thanks again,

Kevin