Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
May be this one
= COUNTROWS ( FILTER ( ALLSELECTED ( Append1[Product_Name] ), CALCULATE ( SUM ( Append1[QTY] ) ) > 5 ) )
= COUNTROWS ( FILTER ( ALLSELECTED ( Append1[Product_Name] ), CALCULATE ( SUM ( Append1[QTY] ) ) > 0 && CALCULATE ( SUM ( Append1[QTY] ) ) < 5 ) )
Hi,
Formula should be written as below;
Calculate(count(your table name(product), filter(your table name, table name [stock qty]>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
May be this one
= COUNTROWS ( FILTER ( ALLSELECTED ( Append1[Product_Name] ), CALCULATE ( SUM ( Append1[QTY] ) ) > 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
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
= COUNTROWS ( FILTER ( ALLSELECTED ( Append1[Product_Name] ), CALCULATE ( SUM ( Append1[QTY] ) ) > 0 && CALCULATE ( SUM ( Append1[QTY] ) ) < 5 ) )
Thank you, for all your help.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
93 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |