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

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