Forum Discussion
Bar Chart w/specific % calculation
I'm trying to calculate the overall "In Stock" % for the following. Within excel this would be a simple calculation for me using a COUNTIFS statement, however trying to use Power BI and DAX I'm struggling. The goal I have is to be able to put this into a bar chart to automatically see out in stock % of items based on a refreshable SQL link for given items in a parameter.
In the attached chart I want to count all items that have an 'Inventory Class' = "A" and Stock Out = "N". Then I want to divide that number by the over count of Inventory Class = "A" items. In the example the final in stock % would equal 60%.
I love the visual tools in Power BI, it's that I just need to get better with DAX.
In general, COUNTIF is replaced with CALCULATE in DAX. Something like this:
Measure 4 = CALCULATE(COUNT([Part #]),FILTER(Inventory,Inventory[Stock Out]="N")) / CALCULATE(COUNT([Part #]),ALLEXCEPT(Inventory,Inventory[Inventory Class]))
Make sure the measure is formatted as % in your model.
3 Replies
In general, COUNTIF is replaced with CALCULATE in DAX. Something like this:
Measure 4 = CALCULATE(COUNT([Part #]),FILTER(Inventory,Inventory[Stock Out]="N")) / CALCULATE(COUNT([Part #]),ALLEXCEPT(Inventory,Inventory[Inventory Class]))
Make sure the measure is formatted as % in your model.
This works great when using the formula this morning. It's very different right now, but in time I'm sure I will figure out the new formula types.
Thank you for the help.
Hi w100ewd,
Have you resolved your issue? Please follow Greg_Deckler's DAX format and try. Don't feel to ask if you have any other issue.
Best Regards,
angelia
