Formula Help

[Anonymous]

So I have a key for every vendor and every vendor may or may not have $ sales for the month. I would like to count how many vendors have $ sales each month, so wherever the column is >0. 

But the Data is in this fashion:

Vendor A $50

Vendor A $25

Vendor A $25

Vendor B $20

Vendor D $10

Vendor D $50

So each vendor can have multiple rows of account sales. How do I count where it's greater than 0, but summarize that by the vendor id? 

I then want to be able to count how many vendors did not have any sales this month. So they will have no rows in this table - since $0. All possible vendor IDs that could have sales are a field, can I then compare them against the vendors that were >0?

What's the best way to solve this? 

[haozhong · Accepted solution]

You need to have another table with all Vendor IDs.

Then in this table add another columm and put in this DAX formula (something like this)

NumberOfSalesMade = CALCULATE(COUNTA(SalesTable[VendorID]),FILTER(ALL(SalesTable),SalesTable[VendorID]=(VendorIDTable[VendorID])))