Forum Discussion

datadmin-austin's avatar
datadmin-austin
Icon for Helper I rankHelper I
3 years ago
Solved

Group by Percentage Range

Hello All,   I am trying to get a count of percentages within a range, most likely using DAX. See sample below: Data: Inspection Score: 85% 85% 90% 70% 75% 100% 92% 75%...
Need Help
  • FreemanZ's avatar
    FreemanZ
    3 years ago

    hi datadmin-austin 

    try to create a bunch of measures like:

    Scores less than 70% = 
COUNTROWS(
    FILTER(
        TableName,
        TableName[Score]<0.7
    )
)

Scores between 70-79.99% = 
COUNTROWS(
    FILTER(
        TableName,
        TableName[Score]>=0.7
            &&TableName[Score]<=0.799
    )
)

Scores between 80-89.99% = 
COUNTROWS(
    FILTER(
        TableName,
        TableName[Score]>=0.8
            &&TableName[Score]<=0.8999
    )
)

Scores between 90-99.99% = 
COUNTROWS(
    FILTER(
        TableName,
        TableName[Score]>=0.9
            &&TableName[Score]<=0.9999
    )
)

Scores abvoe 99.99% = 
COUNTROWS(
    FILTER(
        TableName,
        TableName[Score]>0.9999
    )
)

     

    verified with the multi-row card visual and worked like this:

     

  • datadmin-austin's avatar
    datadmin-austin
    Icon for Helper I rankHelper I
    3 years ago

    amitchandak Thank you for your message. I had to use the steps below to complete this:

     

    1. Create a measured column: 

     

    Percentage Ranking = 
SWITCH(
    TRUE(),
    [InspectionScore] < .7000, "Very Poor",
    [InspectionScore] < .8000, "Poor",
    [InspectionScore] < .9000, "Good",
    [InspectionScore] < .9999, "Very Good",
    "Perfect"
)​

     

    • Create a measure:

     

    Score(70-80%) = 
CALCULATE(
    COUNT('Inspection Data'[Percentage Ranking]),
    FILTER('Inspection Data',
        'Inspection Data'[Percentage Ranking] = "Poor"
    )
)

     

    I believe this should work. I was hoping to find a measure that did not require a measured column, is that possible?