Future-Proof Your Career, Master Data Skills + AI

Blog

Blog

# Inventory Management Dashboard In Power BI

by | 7:00 pm EST | December 16, 2020 | Power BI

In this tutorial, you’re going to learn the complex measures and DAX formula used to create an inventory management dashboard in Power BI.

As a seller, you have to know how many products you need to re-order and stock and how much these will cost you.

Use these DAX formula patterns to get those calculations and results.

## Checking The Inventory Level

Go to your Inventory Stock Control table and look at the Item Reorder Quantity column.

You can also see the Reorder Level column which is used as a scale for the quantities in your inventory. If the order quantity is less than the reorder level, you need to re-order.

You need to evaluate the total cost based on the item reorder level which is quantity multiplied by the cost per item.

For this calculation, create a new measure and name it Inventory Restocking Costs. Next, use the SUMX function to work the inventory stock table and the reorder level. Multiply that by the cost per item.

If you drag that measure into a card, you’ll see the result being 310K. However, it’s not evaluating if you are at the reorder level. All it’s doing is going through every row and cost per item multiplied by the item re-order quantity.

For the number to count up, you need to evaluate whether the stock quantity is less than or equal to the reorder level. To do that calculation, you need to add functions to the measure.

First, add CALCULATE at the beginning. Then after the SUMX function, add the FILTER function. Inside FILTER, put the inventory stock table. Next, evaluate if the stock quantity is less than or equal to the reorder level. If it does, return that number.

After that, you can see the change in the inventory restocking value which is 8,720.

To make it more logical, multiply the factors inside FILTER by 2. The new value in the card is now the expected inventory re-stocking cost.

## Calculating Warehouse Stocks

If you want to know about the restocking by warehouse, copy the doughnut chart and change the Value to Inventory Restocking Costs and the Legend to WarehouseCode.

The next measure you need to create must calculate the actual quantities sold over a period of time. This helps you gauge if you have enough stock.

So, create a new measure and call it Quantity Sold Over Last 12 Months. Since this is historical data, you only need to work out the last day that you sold a product and from that day, go back to 12 months.

Next, use the VAR function. In the next line, name the variable LastSaleDate and input the LASTDATE function. Find OrderDate and then type RETURN.

After that, use an IF statement so that if the Inventory Stock Quantity is blank, the product won’t show up. Input CALCULATE then use SUM for the Order Quantity.

Add another function called DATESBETWEEN. For that function, use LastSaleDate – 365 as the start date and LastSaleDate as the end date.

If you drag that measure into the table, you’ll see the quantity sold over the last 12 months.

Inventory Management Reports To Show Trends In Sales
Calculate Days Of Zero Stock – Power BI Inventory Management Insights
Inventory Management Insights With Power BI: Comparing Stock To Sales

## Conclusion

The advanced DAX formula patterns discussed in this Power BI tutorial will greatly help you in getting valuable insights from your inventory data. Once you have all the calculations you need, the last step is to make your inventory management dashboard compelling.

I hope you apply this in your own Power BI reports. Check out the links below for more visualization and dashboarding techniques in Power BI.

All the best,

Sam

## Power BI Copilot: Enhancing Data Analysis with AI Integration

Are you ready to elevate your data analysis capabilities? Then let's delve into the realm of Power BI...

## What is Microsoft Fabric? A Guide to Features & Benefits

Imagine a one-stop shop that not only crunches numbers like it's eating cereal for breakfast but also...

## Python AttributeError: ‘dict’ object has no attribute Fix

Python is widely appreciated for its readability and ease of understanding. Among its numerous...

## Power BI vs Tableau: Differences Compared 2023

In the world of data visualization and analytics, two industry-leading data visualization tools stand...

## Pandas Drop Index Column: Explained With Examples

Pandas is a widely used Python library for data manipulation and analysis. One essential functionality...

## Microsoft DP-500 Explained (Bonus Cheat Sheet)

Data is the backbone of businesses these days, and having proof that you know how to handle and make...

## Power BI Financial Dashboard Examples: Let’s Get Specific

Power BI financial dashboards provide a quick and easy way to monitor an organization's financial...

## Power BI Themes: User Guide With Examples

Power BI is a powerful business analytics tool that helps you visualize and analyze data from various...

## Power BI Hierarchy: User Guide With Examples

Imagine being able to look at your data from every which way — from the bird's eye view right down to...

## Power BI Calendar Table: What Is it And How to Use It

Time is of the essence in the world of data analysis, and without a proper understanding of when or how...

## How to Sort Alphabetically in Excel with Multiple Columns

Ever find yourself staring at a heap of info in Excel, scratching your head about how to make it all...