Ranking/ preferences questions

[Shak85]

I have a data set where we are asking people to rank from 1-most preferred to 5-least preferred. Suppose if 500 people responded then each option has 500 respondents but i don't know how to report. 

here is the table with reponses. 

ID	Home	cafe	office	base location	base location
100	2	3	1	4	5
101	1	3	2	5	4
102	4	5	1	3	5
103	5	4	3	2	1
104	3	1	4	5	2
105	2	5	1	3	4

let suppose if we group all in a single question and unpivot the data

we get follwing count of each option

Q	1	2	3	4	5
HOME	177	207	77	48	29
CAFE	6	81	192	125	113
OFFICE	330	118	53	25	17
BASE LOCATION	35	85	143	196	64
UNUSAUL	9	40	58	121	289

from this i can tell office  is first first preference, Home is second, cafe is third, base location is fourth and unusual is fifth. Clearly this is not best way to report this data. Can anyone help me, how we can best report this?

Thanks.

[harshnathani]

Hi @Shak85 ,

Please see if this video is helpful

https://www.youtube.com/watch?v=eza1XbeD2Hc

Regards,

Harsh Nathani

[Shak85]

Thanks @harshnathani, I want to replicate this calculation in power BI, This is exactly i'm looking for but don't know how to calculate same way in power bi?

[harshnathani]

Hi @Shak85 ,

Please find the link to the file.

https://drive.google.com/file/d/1X_-r0zB9QvP5f9lGh9ZdY4Mbnb4zpxd5/view?usp=sharing

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[Shak85]

Thanks.  @harshnathani  ,for doing this but i think this is not quiet right as you are just switching values but what we should be doing is multiplying total response (as response is count of each selected option) for each option such as if out of 265 respondents115 people selected working from home as first option and 100 slected office as seecond preference and 50 put cafe as their least preferred option.

I want to calculate Total in the below table but need this Dax 

I need help with DAX with , where we say, If Question is "Home and Answercode-1 then multiple response (which also a measure and count of total who selected option 1)  with 3 and if Question is "office" and answer-code-2 then multiple response with 2, and if Question is "Cafe" and answer-code-3 then multiple response with 1" 

Question	Answer-code-1	Answer-code-2	Answer-code-3	DAx needed for this	Total
home	115	100	50	=115*3+100*2+50*1	615
office	100	115	50	=100*3+115*2+50*1	580
cafe	50	50	165	=50*3+50*2+165*1	415

Thanks.

[harshnathani · Accepted solution]

Hi @Shak85 ,

The calculation is based on Value * Count of Values 

So base location has value  3, 5, 2, 3 ,5 , 4.  Value comes to 5*2 + 4*1 + 3*2 + 2*1 = 22 (See the pbix by filtering baselocation in the data view)

Now since 5 is least preferred,  base location will get the highest preference i.e the bar of base location will be highest which is what you do not want as base location is least preferred and should have the smallest bar. 

Thus new value just reverses your values i.e 5 becomes 1 and 1 becomes 5 . Thus a new Value coumn was calculated. This will give you the base location value as 14.

Value become 22 if you put Value in the visual below. Become 14 when New Column is put in the image below.

But Calculation is on weighted average.

Thanks and Regards,

Harsh Nathani

[Shak85]

@Thank you very much for your help @harshnathani 

[mahoneypat]

Please see this M expression in a blank query to see how to do the unpivot transform.  Then you can simply create a Matrix visual with the Question on the Rows, the Values (Ranking) on the Columns, and a distinct count of the IDs in the values area.  Pic of matrix created below.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZY2xEQAgCAN3obYQkGk89l/DqKDeWaThP6R34lqpkCCKMNIQIy+Tcdw0HFt8MznudbKnx71dDta+rck3s2cnf2LPBw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Home = _t, cafe = _t, office = _t, #"base location" = _t, unusual = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Home", Int64.Type}, {"cafe", Int64.Type}, {"office", Int64.Type}, {"base location", Int64.Type}, {"unusual", Int64.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ID"}, "Attribute", "Value"),
#"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Question"}})
in
#"Renamed Columns"

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[amitchandak]

@Shak85 , create a subcategory Rank in unpivot position; preference  Rank and Option(Office, home) filter for 1

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/3...

unpivot: https://radacad.com/pivot-and-unpivot-with-power-bi
Transpose : https://yodalearning.com/tutorials/power-query-helps-transposing-data/

[Shak85]

Thanks @amitchandak for your reponse. Where shell i add the subcategory in Power query where i unpivot the question and answer or just in data view of the table?

Thanks