Showing results for 
Search instead for 
Did you mean: 
Frequent Visitor

Help Calculating Available Quantity Correctly

Got a data related issue that is being fixed in the background but could take a while, so I am looking for a DAX Band-Aid to just let me work with what I've got for right now. Our company database stores an "Available Quantity" field to reflect how much we have of a particular item that is available for purchase (either in a warehouse or in transit to us). However, this field does not contain any 0's and instead only has blank rows for the Quantity column. There is also an "Available Quantity %" field that SHOULD be calulcating the number of items in a category for which there is at least 1 unit available, and then dividing this by the total number of distinct items in that category. For instance, we sell 75 different items in the "Desk" Category, and if 19 of these desks had at least 1 unit available for purchase, the "Available Quantity %" would be 25%.


This is clearly not the case though, and our data team will hopefully resolve why they all say 100% soon. However, for now, I was hoping to use DAX to get the correct % that I need, and I have created a measure that simply gives the Available Quantity, for each item, but replaces blank values with 0 (the 4th column in the table on the right, "MSR - All Available Quantity"). I am TRYING to get a sum for either all the items in a category that HAVE availabilty (> 0) or do NOT have it (blank or = 0), as I only need one or the other to be able to divide this by the total number of items in the category and get an accurate %. I have tried COUNTROWS(), ISBLANK(), SUMX(), and anything I can think of but I cannot get a good measure created. I even made a measure to flag items that gives 1 if there is a unit for purchase and 0 otherwise, for every single item we sell, and was hoping to filter by category and take a sum, but this has proven difficult... Please help!!!

Solution Sage
Solution Sage

Can you please post more information about the involed tables, columns. Perhaps relationships? 
Also some example data (NO screenshot) with your desired result? 
Without it is very difficult to help. 

Helpful resources

PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Top Solution Authors
Top Kudoed Authors