Count values in column greater than or equal to different values

[devinep5]

Hi,

I've been using the code below to separate values in a column into the ranges below.

However, my graph is showing only the first true value, "1 & Above" and ignoring the rest of the data ranges.

How would I get a count for the numbers that fall into ea

OverallStanineRangeAbove =

SWITCH (

TRUE(),

PT_1819[Overall Stanine] >= 1, "1 & Above",

PT_1819[Overall Stanine] >= 2, "2 & Above",

PT_1819[Overall Stanine] >= 3, "3 & Above",

PT_1819[Overall Stanine] >= 4, "4 & Above",

PT_1819[Overall Stanine] >= 5 , "5 & Above",

PT_1819[Overall Stanine] >= 6 , "6 & Above",

PT_1819[Overall Stanine] >= 7, "7 & Above",

PT_1819[Overall Stanine] >= 8, "8 & Above",

PT_1819[Overall Stanine] >= 9, "9 & Above",

Blank())

[amitchandak]

@devinep5 , Change the order

OverallStanineRangeAbove =
SWITCH (
TRUE(),
PT_1819[Overall Stanine] >= 9, "9 & Above",
PT_1819[Overall Stanine] >= 8, "8 & Above",
PT_1819[Overall Stanine] >= 7, "7 & Above",
PT_1819[Overall Stanine] >= 6 , "6 & Above",
PT_1819[Overall Stanine] >= 5 , "5 & Above",
PT_1819[Overall Stanine] >= 4, "4 & Above",
PT_1819[Overall Stanine] >= 3, "3 & Above",
PT_1819[Overall Stanine] >= 2, "2 & Above",
PT_1819[Overall Stanine] >= 1, "1 & Above",
Blank())

[devinep5]

Apologies, I hadn't unfiltered the data before replying to you.
The bottom graph is what it looks like now. As you can see, "1 & above" should be 100% (because everyone has achieved at elast a 1 mark), biut the percentage as you can see in the screenshot below is showing only 1%.

[v-yingjl · Accepted solution]

Hi @devinep5 ,

I have created this simple table initially:

Is this your expected result?

If so, create a [OverallStanineRangeAbove] calculated column as x-axis using the previous formula:

OverallStanineRangeAbove = 
SWITCH (
TRUE(),
PT_1819[Overall Stanine] >= 9, "9 & Above",
PT_1819[Overall Stanine] >= 8, "8 & Above",
PT_1819[Overall Stanine] >= 7, "7 & Above",
PT_1819[Overall Stanine] >= 6, "6 & Above",
PT_1819[Overall Stanine] >= 5, "5 & Above",
PT_1819[Overall Stanine] >= 4, "4 & Above",
PT_1819[Overall Stanine] >= 3, "3 & Above",
PT_1819[Overall Stanine] >= 2, "2 & Above",
PT_1819[Overall Stanine] >= 1, "1 & Above",
Blank()
)

Then create a [Percentage] calculated column as y-axis using this formula:

Column = 
VAR _count =
    COUNT ( PT_1819[Overall Stanine] )
VAR _C1 =
    COUNTX (
        FILTER ( 'PT_1819', 'PT_1819'[Overall Stanine] >= 1 ),
        [Overall Stanine]
    )
VAR _C2 =
    COUNTX (
        FILTER ( 'PT_1819', 'PT_1819'[Overall Stanine] >= 2 ),
        [Overall Stanine]
    )
VAR _C3 =
    COUNTX (
        FILTER ( 'PT_1819', 'PT_1819'[Overall Stanine] >= 3 ),
        [Overall Stanine]
    )
VAR _C4 =
    COUNTX (
        FILTER ( 'PT_1819', 'PT_1819'[Overall Stanine] >= 4 ),
        [Overall Stanine]
    )
VAR _C5 =
    COUNTX (
        FILTER ( 'PT_1819', 'PT_1819'[Overall Stanine] >= 5 ),
        [Overall Stanine]
    )
VAR _C6 =
    COUNTX (
        FILTER ( 'PT_1819', 'PT_1819'[Overall Stanine] >= 6 ),
        [Overall Stanine]
    )
VAR _C7 =
    COUNTX (
        FILTER ( 'PT_1819', 'PT_1819'[Overall Stanine] >= 7 ),
        [Overall Stanine]
    )
VAR _C8 =
    COUNTX (
        FILTER ( 'PT_1819', 'PT_1819'[Overall Stanine] >= 8 ),
        [Overall Stanine]
    )
VAR _C9 =
    COUNTX (
        FILTER ( 'PT_1819', 'PT_1819'[Overall Stanine] >= 9 ),
        [Overall Stanine]
    )
RETURN
    SWITCH (
        TRUE (),
        'PT_1819'[Overall Stanine] >= 9, _C9 / _count,
        'PT_1819'[Overall Stanine] >= 8, _C8 / _count,
        'PT_1819'[Overall Stanine] >= 7, _C7 / _count,
        'PT_1819'[Overall Stanine] >= 6, _C6 / _count,
        'PT_1819'[Overall Stanine] >= 5, _C5 / _count,
        'PT_1819'[Overall Stanine] >= 4, _C4 / _count,
        'PT_1819'[Overall Stanine] >= 3, _C3 / _count,
        'PT_1819'[Overall Stanine] >= 2, _C2 / _count,
        'PT_1819'[Overall Stanine] >= 1, _C1 / _count,
        BLANK ()
    )

It will get the above result in the column chart.

Attached my sample file that hopes to help you: Count values in column greater than or equal to different values.pbix

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[devinep5]

Thanks for the reply, however, it's still showing only one data range, "1 & above".

Essentially what I'm trying to do is have on the X-axis, total achieving "1 & above", "2 & above", etc.