Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Help with Ranking and Quintile Calculation

  Hello everyone,   I’m  working on a mock schools test scores dashboard in Power BI, and I need help calculating: The average score of the lowest and highest quintile  The ability for u...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    1 year ago

    Hi,

    I am still unable to understand the desired result.  If possible, can you share the download link of an MS Excel file.  In that file, via Excel formulas, show the desired result.  I will try to translate those formulas into measures.

Anonymous's avatar
Anonymous
Not applicable
1 year ago

 

 

Hi bhanu_gautam  Thanks I tried to update the Quintile dax as you said but it gives me this message  and I am not sure why .  Any ideas much appreaciated . 

 

 

 

 

  • techies's avatar
    techies
    Super User
    1 year ago

    Hi Anonymous to assign quintiles, create this calculated column

     

    Quintile n =
    VAR TotalStudents = COUNTROWS(ALL('Sheet3'))
    VAR QuintileSize = INT(TotalStudents / 5)
    VAR RankValue = RANKX(ALL('Sheet3'), 'Sheet3'[Result], , ASC, DENSE)

    RETURN
        SWITCH(
            TRUE(),
            RankValue <= QuintileSize, "1st Quintile",
            RankValue <= QuintileSize * 2, "2nd Quintile",
            RankValue <= QuintileSize * 3, "3rd Quintile",
            RankValue <= QuintileSize * 4, "4th Quintile",
            "5th Quintile"
        )
     
     
    And measures as this
     
    Avg_Highest_Quintile =
    CALCULATE(
        AVERAGE('Sheet3'[Result]),
        'Sheet3'[Quintile n] = "5th Quintile"
    )
     
    Avg_Lowest_Quintile =
    CALCULATE(
        AVERAGE('Sheet3'[Result]),
        'Sheet3'[Quintile n] = "1st Quintile"
    )
     
     
    For score range selection, create a parameter
     
     
    • v-nmadadi-msft's avatar
      v-nmadadi-msft
      Community Support
      1 year ago

      Hi Anonymous,

      May I ask if you have resolved this issue with the suggestions provided by techies ? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

      Thank you.