Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
kflash2116
Frequent Visitor

Appropriate Aggregation

Hi All,

 

Very new to PowerBI, but want to try and learn it since my company is moving towards using it and getting rid of our Tableau license.

 

I made some sample data that I was testing in Tableau and trying to replicate it in PowerBI; the chart I was trying to replicate looks like this:

 

kflash2116_0-1712799773350.png

 

It compares Inventory data to Forecast data in aggregation and develops an in stock percentage.

 

I am trying to accomplish the same thing in PowerBI but can't quite figure it out.  I have shared a link below that will lead to my workbook, with the data embedded within.  Any suggestions would be greatly appreciated!

 

https://drive.google.com/drive/folders/1G-4hkiuBeu-01SEhnQnUSugp9Om49X21?usp=sharing

3 REPLIES 3
Wilson_
Super User
Super User

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?




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





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:

kflash2116_0-1712803412915.png

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.




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.