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
Shak85
Helper I
Helper I

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. 

 

IDHomecafeofficebase locationbase location
10023145
10113254
10245135
10354321
10431452
10525134

 

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

we get follwing count of each option

 

Q12345
HOME177207774829
CAFE681192125113
OFFICE330118532517
BASE LOCATION358514319664
UNUSAUL94058121289

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.

1 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)

 

 

3.JPG

 

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.

 

 

1.jpg2.JPG

 

But Calculation is on weighted average.

 

 

 

 

Thanks and Regards,

Harsh Nathani

 

 

View solution in original post

9 REPLIES 9
harshnathani
Community Champion
Community Champion

Hi @Shak85 ,

 

Please see if this video is helpful

 

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

 

 

Regards,

Harsh Nathani

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?

 

Hi @Shak85 ,

 

 

Please find the link to the file.

 

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

 

 

1.jpg

 

 

Regards,
Harsh Nathani

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

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" 

 

QuestionAnswer-code-1Answer-code-2Answer-code-3DAx needed for thisTotal
home11510050=115*3+100*2+50*1615
office10011550=100*3+115*2+50*1580
cafe5050165=50*3+50*2+165*1415

 

Thanks.

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)

 

 

3.JPG

 

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.

 

 

1.jpg2.JPG

 

But Calculation is on weighted average.

 

 

 

 

Thanks and Regards,

Harsh Nathani

 

 

@Thank you very much for your help @harshnathani 

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"

 

ratings.png

 

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

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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

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.