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
This is what I want it to look like
|Year_Purchase||Stock Greater Than 5|
What is the formual I need to get the Stock Greater Than 5?
Solved! Go to Solution.
This is my formual I have.
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.
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?
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?
We're excited to announce our first cross-community 'Can You Solve These?' challenge!
Visit our Community Blog for articles, guides, and information created by fellow community members.
Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.
Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.