SUM the total of Averages and then divide that by Sum of Quantity

[NBOnecall]

Hi,

I have a table that gives me inventory data each day for items in our catalog. I am getting the Average Available a day over a time frame of 90 days. I want to take the total of those averages for all out items and divide it by the total of quantity sold to get our Run Rate. Right now on the subtotal it only gives the average of all available divided by the total quantity sold.

Here is the Sell Through mesure -

Available divided by Quantity = 
DIVIDE(
        SUM('ns SalesRegister'[Quantity]),
	SUM('ns vwInventorySnapshot'[Available])	
)

Here is what the table looks like currently.

When I export to excel and sum column "Average Available" and then do the sell through equation, it gets the number that I am expecting, versus what is being outputed in Power BI.

Thank you,

Noel

[lc_finance]

Hi @NBOnecall ,

The problem is that Power BI is reapplying your formula on the total instead of adding up each row to obtain the total.

Depending on your data model, there can be two solutions: either using a calculated column or using SUMX.

I wrote a post exactly on this topic. You can find here:

https://finance-bi.com/power-bi-totals-incorrect/

Does this help you?

If it doesn't, can you share a sample of your Power BI file? This will make it easier to help you.

You can upload the file to One Drive, Google Drive or similar tool and share a link here.

Regards,

LC

Interested in Power BI and DAX templates? Check out my blog at www.finance-bi.com

[NBOnecall]

I am still lost. My example I have two tables. One table is for the Sales and has the internalID and the other table has the available quantity with also the internalID. I need to take the average of inventory in the past 90 days and find out the number of sales in that same time frame. Then divide to get the sell through rate.

Thanks.

[lc_finance]

Hi @NBOnecall ,

I believe the formula for your need is AVERAGEX.

I will help you with the implementation of this formula. For that, could you share a sample Power BI file, and the result you expect?

LC

[NBOnecall]

https://www.dropbox.com/s/vzm3o4jsb37j4cb/SellThroughTest.pbix?dl=0

Expected Results -

Thank you,

Noel

[lc_finance]

Hi @NBOnecall ,

You can find the solution attached.

The problem was that the total of the 'average on hand'. It was calculating an average of all products, instead of calculating an average of each product and then adding them up.

I created the following average formula to fix it:

Average on hand = 

SUMX(VALUES('ns vwInventorySnapshot'[ItemInternalID]),AVERAGE('ns vwInventorySnapshot'[OnHand]))

the VALUES function creates a list of all the SKUs, the AVERAGE finds the average on hand for each SKU, and finally, SUMX adds up each SKU to find a total.

I then modified the On Hand divided by quantity formula to take into account the new measure created.

This is what it looks now:

I hope this helps you! Do not hesitate if you have any more questions.

LC

Interested in Power BI and DAX templates? Check out my blog at www.finance-bi.com

[NBOnecall]

@lc_financeSo I went and update my Power Bi file with the new equations and now I am getting a a sum that is more in Power BI total line then when I export to Excel and then sum it. I have 96k average on hand in Power BI, but when I export and sum I get 77k. Is there something I am missing?

[lc_finance]

Hi @NBOnecall ,

I'd need to take a look at the Power BI file to understand better.

Average on hand = 

SUMX(VALUES('ns vwInventorySnapshot'[ItemInternalID]),AVERAGE('ns vwInventorySnapshot'[OnHand]))

The formula finds all products based on their ItemInternalID, then for each product, it calculates the average of the OnHand column.

Finally, it adds up the average on each product to find a total.

Maybe there are multiple products with the same ID? Hard to say without checking the file

LC

Interested in Power BI and DAX templates? Check out my blog at www.finance-bi.com

[NBOnecall]

Maybe there are multiple products with the same ID? Hard to say without checking the file

Where would you think the duplication would be the item table? Obviously the InventorySnapShot table would have duplicates as each row is shown for each day we had inventory balances.