Count if <= row by row calculation

Question

I am trying to count the number of results that are <= 25%.

See bellow for example data

I am trying to create a measure that counts if "Pallet Utilization" is <= 25%.

"Pallet Utilization" is not a column within the data, it would need to be calculated within the measure.

From my understanding, i need to ask the measure to calculate Row by Row?

Bellow is my attempt at doing this however it is returning a count of all rows

AC_Less25 =
CALCULATE (
    COUNTAX (
        Chilterns_STORAGE,
        DIVIDE (
            DIVIDE ( Chilterns_STORAGE[NO_CASES], Chilterns_STORAGE[NO_PALLETS] ),
            Chilterns_STORAGE[POU_MAX]
        ) <= 0.25
    )
)

Not very experienced with DAX so any help appreciated.

Thanks

Yes, i edited the typo. Either way, i still get the same result no matter which number is used. — leTurner
please provide your data sample. If you have more than 1 table, post an image of the data model. — RADO

Frostytheswimmer Frostytheswimmer · Accepted Answer · 2019-06-25T15:06:38

So you could use COUNTX, but you can also just use COUNT and add a calculated column.

Add a new Calculated Column:

Pallet Utilization =
DIVIDE (
    DIVIDE ( Chilterns_STORAGE[NO_CASES], Cilterns_STORAGE[NO_PALLETS] ),
    Chilterns_STORAGE[POU_MAX],
    BLANK ()
)

And then add the new measure:

AC_Less25 =
CALCULATE (
    COUNT ( Chilterns_STORAGE[Pallet Utilization] ),
    FILTER ( Chilterns_STORAGE, Chilterns_STORAGE[Pallet Utilization] <= .25 )
)

EDIT:

If you're dead-set on using COUNTX, something like this would help. In a COUNTX ( or any 'X' measure for that matter ), you define the table you want to iterate over, then provide what it is counting/summing/averaging as the second parameter. So conditions are placed on the table like so:

AC_Less25 = COUNTX(
FILTER(Chilterns_STORAGE, 
    DIVIDE(
        DIVIDE ( Chilterns_STORAGE[NO_CASES], Chilterns_STORAGE[NO_PALLETS] ),
    Chilterns_STORAGE[POU_MAX]) <= .25),
Chilterns_STORAGE[NO_PALLETS])

Please note that I'm not sure my Pallet Utilization is correct because I'm not getting the same numbers as you are in your OP... But the screenshot speaks for itself and the CountX above will still do what you want it to do, provided you tweak the conditions in the first parameter of the CountX: DIVIDE(DIVIDE ( Chilterns_STORAGE[NO_CASES], Chilterns_STORAGE[NO_PALLETS] ), Chilterns_STORAGE[POU_MAX]) <= .25))

Count if <= row by row calculation

1 Answers