cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
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_PurchaseProductStock_QTY
2010Product110
2010Product24
2011Product36
2011Product420
2011Product52

 

This is what I want it to look like

Year_PurchaseStock Greater Than 5
20101
20112

 

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

 

Regards


GrahamR99

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User III
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

View solution in original post

Highlighted
Super User III
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

View solution in original post

8 REPLIES 8
Highlighted
Helper I
Helper I

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

Hi,

 

Formula should be written as below;

 

 

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

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

 

PowerBI_Matrix.jpg

 

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

View solution in original post

Highlighted
Helper II
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
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
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))

PowerBI_Matrix_part2.jpg

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

View solution in original post

Highlighted
Helper II
Helper II

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

Thank you, for all your help. Smiley Very Happy 

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

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

Community Blog

Community Blog

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

Upcoming Events

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

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