Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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.  ...
Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    6 years ago

    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

     

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Thanks Anonymous, 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?

 

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    6 years ago

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks.  Anonymous  ,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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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