Need calculate number with two conditions

[laba031095]

I have table:
table








There was a need to count the number of stores under two conditions.
1) There are 2/3/4 necessary SKUs in the store
2) The amount of sales in the store is more than $30

for example, I need to find a store that sells $30 or more and has SKU1 and SKU2

In this case, the answer will be "3"

If you need to find a store that sells in the amount of $ 30, and where there are SKU1 and SKU4, the answer will be "1"

[Pragati11]

Hi @laba031095 ,

This is easily achievable. Can you share sample data by copying from excel and pasting it here?

you have shared a screenshot for which I will have to type the data to try at my end.

[laba031095]

Shop	SKU	Sum
Shop1	Sku1	10
Shop1	Sku2	15
Shop1	Sku3	5
Shop2	Sku1	10
Shop2	Sku2	20
Shop3	Sku1	5
Shop3	Sku2	10
Shop3	Sku3	5
Shop3	Sku4	10

[V-lianl-msft · Accepted solution]

Hi @laba031095 ,

Try to create a measure like below:

Measure 2 = 
var count_1 =  COUNTROWS(FILTER('Table','Table'[SKU] in VALUES('Table 2'[SKU])))
var count_2 = COUNTROWS('Table 2')
var sum_ = CALCULATE(SUM('Table'[Sum]),ALLEXCEPT('Table','Table'[Shop]))
return if(count_1=count_2,CALCULATE(DISTINCTCOUNT('Table'[Shop]),FILTER('Table',sum_>=30)))

See if it will meet your needs

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.