October 28 & 29: Experts share their secrets on how to pass the Fabric Analytics Engineer certification exam—live. Learn more
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?
Solved! Go to 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])))
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])))
User | Count |
---|---|
105 | |
99 | |
98 | |
86 | |
49 |
User | Count |
---|---|
162 | |
142 | |
132 | |
102 | |
63 |