cancel
Showing results for
Did you mean:
Highlighted
Helper II

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

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

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User III

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

@GrahamR99

May be this one

```=
COUNTROWS (
FILTER (
ALLSELECTED ( Append1[Product_Name] ),
CALCULATE ( SUM ( Append1[QTY] ) ) > 5
)
)```
Try my new Power BI game Cross the River
Highlighted
Super User III

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

@GrahamR99

```=
COUNTROWS (
FILTER (
ALLSELECTED ( Append1[Product_Name] ),
CALCULATE ( SUM ( Append1[QTY] ) ) > 0
&& CALCULATE ( SUM ( Append1[QTY] ) ) < 5
)
)```
Try my new Power BI game Cross the River
8 REPLIES 8
Highlighted
Helper I

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

Hi,

Formula should be written as below;

Highlighted
Helper II

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

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

Super User III

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

@GrahamR99

May be this one

```=
COUNTROWS (
FILTER (
ALLSELECTED ( Append1[Product_Name] ),
CALCULATE ( SUM ( Append1[QTY] ) ) > 5
)
)```
Try my new Power BI game Cross the River
Highlighted
Helper II

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

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

Highlighted
Super User III

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

This is strange

May be use <=
Try my new Power BI game Cross the River
Highlighted
Helper II

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

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

Highlighted
Super User III

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

@GrahamR99

```=
COUNTROWS (
FILTER (
ALLSELECTED ( Append1[Product_Name] ),
CALCULATE ( SUM ( Append1[QTY] ) ) > 0
&& CALCULATE ( SUM ( Append1[QTY] ) ) < 5
)
)```
Try my new Power BI game Cross the River
Highlighted
Helper II

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

Thank you, for all your help.

Announcements

#### August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

#### Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

#### Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

#### Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors