Forum Discussion
Appropriate Aggregation
Hey kflash2116,
Thanks for providing the pbix file. That's very useful. Can you also describe in words, maybe with a numerical example, what the calculation you want actually is?
Hi! Thank you for your reply; yeah, shouldn't have just assumed anyone could intuit what I'm after after just posting a graph with no info.
My goal is to try and get an "in-stock" percentage for each month at the Brand Quality level. I'm pretty bad at trying to put it into words, but below I added a screenshot I took of an Excel pivot table that shows the rough process I followed to build the Tableau chart calculations, for the month of December:
The "In Stock" is taken by comparing the inventory to the forecast; if the inventory is 0, the in-stock is 0. If the inventory is less or equal to the forecast, than the in-stock is equal to the inventory. If the inventory is greater than the forecast, than the in-stock is equal to the forecast.
The "Out of Stock" is the remainder of the forecast if the inventory doesn't cover it--in the first line of the screenshot above, the difference between the inventory and forecast for Brand Quality 1 is 665 and some change, so that is what is out of stock.
Once all the "in stock" and "out of stock" numbers are calculated for each month, the total "in stock" quantities are added, and it is then divided by the total "in stock" quantity added to the total "out of stock" quantity to get that 81.14% number.
kflash2116,
haha no worries! I dug around the file a little bit looking at what you'd tried so far to try to figure it out but thought I'd just ask since you said it wasn't working for you anyway.
Anyway, here's a link to my updated file. The main roadblock is doing it with calculated columns wasn't going to work since you wanted a non-additive calculation done as a different level of granularity than your table's grain. (That's just a fancy way of saying you needed to tweak your table before doing your calculations and it wouldn't work as is.)
I've included two different "styles" of calculating your result: one fully contained measure, and a combination of a few measures in case you needed to show the individual pieces (ie: in stock, out of stock) too.Since you mentioned wanting to learn Power BI because your workplace was adopting it, you can't go wrong with SQLBI's free courses. They're my go to recommendation for people new to Power BI.
Feel free to get ahold of me if you get really stuck along the way. 😄
----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)P.S. Need a more in-depth consultation for your Power BI data modeling or DAX issues? Feel free to hire me on Upwork or DM me directly on here! I would love to clear up your Power BI headaches.