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

Highlighted
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.

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors