Countif multiple conditions met

[dfort]

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 

[Whitewater100]

Hello:

Please see attached file. Thanks..https://drive.google.com/file/d/1sk69sZwTd9XZkLjEilXDHBZS9HfPtC20/view?usp=sharing 

[dfort]

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.

[dfort]

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 

[Anonymous]

Hi @dfort ，

Could you please share some raw data in your involved tables on the matrix with Text format and your final expected result with backend logic and special example screenshot with us in order to provide you a suitable solution?  According to your description, it seems that you already get the total qty and total stock for per part number, could you please also provide their formula? Do you want to get the count of part number which stock qty is less than total qty and the value of Make/Buy is "Purchase"? It is better if you can provide a simplified pbix file. Thank you.

Best Regards

[tamerj1]

Hi @dfort 
Do you mean you need to count how many time the "Stock Less Required" is -ve (less than 0)?
If so (and given that you should be using a matrix visual) then it should not be an issue. Can you please share your two measures and I will create the code for the third one. But you need to decide where do you want to view the result (In a card visual for example)?

[dfort]

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.

[dfort]

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