Forum Discussion
Count where doesn't exist
I'm struggling to find solution: I have data on 1 row (company, product). I am wanting to pick a product and show all the companies that have NOT bought this product. I also only want to see the companies that have sold 2 or more products.
Data:
Company A Product 1
Company A Product 2
Company A Product 3
Company B Product 2
Company C Product 2
Company C Product 3
Company D Product 2
Company D Product 3
Result needed: I want to be able to select a product ( Product 1) and see list of companies that could still be sold Product 1:
Company C
Company D
I don't want to see Company B because it hasn't been sold 2 or more products.
I then would also like to count how many companies that I could sell Product 1 to, which would be 2.
You may take a look at the posts below.
3 Replies
Sha ,
One way to get if you have company Dimension
Except(Company[Company],table[Company])
Other is create a measure and then use nest two measures for your two requirements
measure = count(Table[Product])+0
Filter measure =calculate(sumx(value(company[company]),[measure]),filter(Table,[measure]=0))
Filter measure 2 =calculate(sumx(value(company[company]),[measure]),filter(Table,[measure]>=2))I created 2 measures but Value didn't work in 2nd measure, should I be using Values? It didn't produce any results.
Product cnt = count(Sheet1[Product])+0 (when I created matrix, 0 didn't show up)Company Cnt = CALCULATE(SUMX(values(Sheet1[Company]),[Product cnt]),FILTER(Sheet1,[Product cnt]=0)) (when I created table for Company, Company Cnt nothing showed up)
You may take a look at the posts below.
