Forum Discussion
Ranking/ preferences questions
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.
Hi Anonymous ,
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
9 Replies
- amitchandak
Super User
Anonymous , 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/367415unpivot: https://radacad.com/pivot-and-unpivot-with-power-bi
Transpose : https://yodalearning.com/tutorials/power-query-helps-transposing-data/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
Hi Anonymous ,
Please see if this video is helpful
https://www.youtube.com/watch?v=eza1XbeD2Hc
Regards,
Harsh Nathani
- mahoneypat
Microsoft Employee
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
