Forum Discussion

datadmin-austin's avatar
datadmin-austin
Helper 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%

 

Output Goal:

Scores less than 70%: 0

Scores between 70-79.99%: 3

Scores between 80-89.99%: 2

Scores between 90-99.99%: 2

Scores above 99.99%: 1

 

Any assistance would be greatly appreciated.

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:

     

4 Replies

  • FreemanZ's avatar
    FreemanZ
    Super User
    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
      Helper 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?