Forum Discussion
Countif multiple conditions met
Hello,
I am trying to create a measure that will give me the quantity of items that meet multiple criterias.
I have part numbers that may show up in the column more than once, so would want to consolidate all same items in order to get the total quantity required for each unique part number
I have a column saying if the item is purchased or made
I have a column that shows the quantity required of each item, so this can be sum'd
I have a column that is a measure looking at Total in Stock less Qty Required to show items that are short.
Long story short
How many "PURCHASE" items required are we short stock on
7 Replies
this formula doesnt return the actual number i am looking for, it just returns a count of how many unique part numbers there are.. so its ignoring me trying to ask for items less than 0
CountPurItemStock2 =CALCULATE(DISTINCTCOUNT('Perseverance BOM'[No.]),FILTER(AllMeasures,AllMeasures[InvCalgaryMinusRequired]<0),'Perseverance BOM'[Make/Buy]="Purchase")
Whitewater100Solution Sage
Hello:
Please see attached file. Thanks..https://drive.google.com/file/d/1sk69sZwTd9XZkLjEilXDHBZS9HfPtC20/view?usp=sharing
Thank you for the reply and time you put into this.
What you created is right for my description of problem, I can see how it led you to that direction.
Here is a version of if that might help what I am trying to do.
In my data set (bill of materials) there is 1020 lines items. The same part number may show up multiple times. So I should consolidate the data down to sum up all the same part numbers and pull that into power bi.. but if I were to avoid doing that and let power bi do it for me in a measure, it would be great.
So.. as you can see 133717 - Gusset Plate A shows up twice, as does other gusset plates.
I want the measure to sum up the unique part numbers so it would know I require 75 Gusset Plate A - Part # 133717.
These items are a purchase part. You can see in the data set there is a Prod. Order part mixed in.
So I am trying to make the measure
- sum up the same part numbers
- are purchase items
- then the qty I require would be compared to my current stock level. If the result is less than 0, it would count the number of less than 0 lines to tell me I am short 245 different part numbers out of the fill list of 1020.
133717 - Gusset Plate A - 75 Required. 0 in stock = -75 <-- short 75, is less than 0 so result would be 1 as in 1 item is short out of 1020 lines.
just want to get a pulse on how many items in the whole project are still outstanding.
Thank you for the reply and time you put into this.
What you created is right for my description of problem, I can see how it led you to that direction.
Here is a version of if that might help what I am trying to do.
In my data set (bill of materials) there is 1020 lines items. The same part number may show up multiple times. So I should consolidate the data down to sum up all the same part numbers and pull that into power bi.. but if I were to avoid doing that and let power bi do it for me in a measure, it would be great.
So.. as you can see 133717 - Gusset Plate A shows up twice, as does other gusset plates.
I want the measure to sum up the unique part numbers so it would know I require 75 Gusset plates.
These items are a purchase part. You can see in the data set there is a Prod. Order part mixed in.
So I am trying to make the measure sum up the same part numbers, are purchase items then the qty I require would be compared to my current stock level. If the result is less than 0, it would count the number of less than 0 lines to tell me I am short 245 different part numbers out of the fill list of 1020.
I was over thinking this.. I already have a measure suming up the total qty required and a measure looking at total stock less the total required.. now I just need to do a count less than 0 of this stock less total qty required. .. if I can figure out how to do a count of a measure
