Product Rank

[zahirr]

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?

[zahirr]

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

[rbriga]

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]

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 · Accepted solution]

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