Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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])))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |