Forum Discussion

zahirr's avatar
zahirr
Regular Visitor
4 years ago
Solved

Product Rank

I have a survey where we asked in the rank of same products as 5 questions -

Rank 1 - A/B/C/D
Rank 2 - A/B/C/D
Rank 3 - A/B/C/D
Rank 4 - A/B/C/D
Any easy way to calculate these options A, B, C, D on the basis of rank weight?

 

 
  • rbriga's avatar
    rbriga
    4 years ago

    Alrright, I see what went wrong.

    We need to divide the value by the number of survey IDs:

    Weighted Rank =
DIVIDE(
    SUM( 'Survey'[Rank Value] ),
    CALCULATE(
        COUNTROWS(
            VALUES( Survey[_id] )
        ),
        ALL( Survey[Value] )
    )
)

    Where "value" is actually the category (food, health, etc.).

4 Replies

  • zahirr's avatar
    zahirr
    Regular Visitor
    4 years ago

    Yes, it did!
    Thanks a lot for the help 🙂 

  • rbriga's avatar
    rbriga
    Icon for Impactful Individual rankImpactful Individual
    4 years ago

    Assuming you have a column Survey[Rank Value] with the results 1,2,3,4,

    Weighted Rank=
DIVIDE(SUM('Survey'[Rank Value]), COUNTROWS('Survey'))

    See the sample table layout that I've assumed you have.

    If it's a pivoted table (a column for each rank), unpivot it usinp the query editor.

     

    • zahirr's avatar
      zahirr
      Regular Visitor
      4 years ago

      Thank you for the suggestions. I already tried reshaping my original data.
      Tried your formula as well, which is not working as well.



      Original Data -

      Reshaped Data-

       

       

      • rbriga's avatar
        rbriga
        Icon for Impactful Individual rankImpactful Individual
        4 years ago

        Alrright, I see what went wrong.

        We need to divide the value by the number of survey IDs:

        Weighted Rank =
DIVIDE(
    SUM( 'Survey'[Rank Value] ),
    CALCULATE(
        COUNTROWS(
            VALUES( Survey[_id] )
        ),
        ALL( Survey[Value] )
    )
)

        Where "value" is actually the category (food, health, etc.).