Count Amount of Product's with a total of greater than 5

[GrahamR99]

Hello

I am looking for a formual for a measure that will count the amount of products in a year that have a stock quantaity greater than 5.

So for example this is my data example

Year_Purchase	Product	Stock_QTY
2010	Product1	10
2010	Product2	4
2011	Product3	6
2011	Product4	20
2011	Product5	2

This is what I want it to look like

Year_Purchase	Stock Greater Than 5
2010	1
2011	2

What is the formual I need to get the Stock Greater Than 5?

Regards

GrahamR99

[Zubair_Muhammad · Accepted solution]

@GrahamR99

May be this one

=
COUNTROWS (
    FILTER (
        ALLSELECTED ( Append1[Product_Name] ),
        CALCULATE ( SUM ( Append1[QTY] ) ) > 5
    )
)

[pbiozkan]

Hi,

Formula should be written as below;

Calculate(count(your table name(product), filter(your table name, table name [stock qty]>5)

[GrahamR99]

Hello

This is my formual I have.

CALCULATE(Count(Append1[Product_Name]),filter(Append1, Append1[QTY]>5))

but it does not work.

I am expecting for 2010 to be 1 and 2011 to be 0.

SKU is the total about of products for that year, so products with greater than 5 should be less than the SKU, as you can see from my table I have more for Grater than 5.

Regards

GrahamR99

[Zubair_Muhammad · Accepted solution]

@GrahamR99

May be this one

=
COUNTROWS (
    FILTER (
        ALLSELECTED ( Append1[Product_Name] ),
        CALCULATE ( SUM ( Append1[QTY] ) ) > 5
    )
)

[GrahamR99]

Hello

COUNTROWS ( FILTER ( ALLSELECTED ( Append1[Product_Name] ), CALCULATE ( SUM ( Append1[QTY] ) ) > 5 ) )

That works, thank you.

How do I do less than 5?

I change it to this

COUNTROWS ( FILTER ( ALLSELECTED ( Append1[Product_Name] ), CALCULATE ( SUM ( Append1[QTY] ) ) < 5 ) )

but that did not work, and I don't know why?

Regards

GrahamR99

[Zubair_Muhammad]

This is strange

May be use <=

[GrahamR99]

Hi

I tried <= but the number is still massive.

Here is the new figure when I change the formual to;

COUNTROWS(FILTER(ALLSELECTED(Append1[Product_Name]),CALCULATE(SUM(Append1[QTY])) <= 5))

Why is it such a big number for the less than 5?

I think it is counting all the zero amounts, how do I take them out so that its greater than 0 but less than 5?

Regards

GrahamR99

[Zubair_Muhammad · Accepted solution]

@GrahamR99

=
COUNTROWS (
    FILTER (
        ALLSELECTED ( Append1[Product_Name] ),
        CALCULATE ( SUM ( Append1[QTY] ) ) > 0
            && CALCULATE ( SUM ( Append1[QTY] ) ) < 5
    )
)

[GrahamR99]

Thank you, for all your help.