CODEWITHSUNDEEP
powerbidax
leTurner
leTurner

Reputation: 109

Count if <= row by row calculation

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

See bellow for example data

enter image description here

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

Upvotes: 1

Views: 115

Answers (1)

Frostytheswimmer
Frostytheswimmer

Reputation: 718

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))

enter image description here

Upvotes: 1

Related Questions