Group by Percentage Range

[datadmin-austin]

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.

[FreemanZ · Accepted solution]

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]

@FreemanZ Thank you very much! This is much more simple and managable.

[amitchandak]

@datadmin-austin , I think you need

Dynamic Segmentation Bucketing Binning
https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1...

Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k

[datadmin-austin]

@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?