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!!!
Hard mode - changing total sum of measure
/* EDIT : created the PBIX file and reformulate the description */ Hi Everyone, I'm new to Power BI & Dax, and I'm struggling with one Dax Formula here is the data structure : with that formula : old_cogs = SUMX('Product',[Fin COGS per 9L BL] *[9L Cs]) I got things right, because the total is the sum of all my rows, and my rows respect my formula : [Fin COGS per 9L BL] *[9L Cs] But once I remove the column Product SKU, I got the result below which is not correct : Yeah In fact, once I removed the SKU product dimensions, the calculation should go back to normal, which is : [Fin COGS per 9L BL] *[9L Cs] Why I still have the sum of each product value ? I also join the PBIX file. PBIX Thanks in advance for people that will try to help me 🙂
