Want grand total of average function to be a sum not an average

Hi,

I have a measure that calculates the average value of inventory for each item in inventory.

Avg Inventory Value = Cost per unit * Avg Inventory balance

The problem is while each individual item is calculating correctly, the grand total should be a sum of Avg Inventory value not the grand total of Cost per unit * grand total of Avg Inventory balance.

Thanks,

Hi @MathewA, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

  • Use the forum search to discover if your query has been asked before by another member.

  • When posting a topic with formula make sure that it is correctly formatted to preformated text </>.

  • Use the proper category that best describes your topic

  • Provide as much context to a question as possible.

  • Include demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

I also suggest that you check the forum guideline https://forum.enterprisedna.co/t/how-to-use-the-enterprise-dna-support-forum/3951. Not adhering to it may sometimes cause delay in getting an answer.

@MathewA,

Please check out this thread below. I think it provides a detailed solution to a scenario that matches yours exactly:

  • Brian

Hi,

I spent some time going over that thread and watching the video’s, but I think I’m missing something. I created an example PBX to help.

A couple of notes.

  1. I start with a transaction table, this has all my normal inventory transactions.

  2. I then create a snapshot table that has the inventory balance on hand each day. I do this because I want to get an average of the inventory balances. Maybe there’s an easier way to do it (although my full table does have 28 million rows so this may or may not help with efficiency as well.)

  3. I want to get the average cost of each inventory item on hand over the period, but the total I want to be the sum of all the inventory balances. Here is the code for that.

  4. One other quick point, the items are in a hierarchy. If that matters.

    Avg Cost Bal = 
    VAR AvgCost = AVERAGE('Inventory Summary'[Cost Bal])
    VAR TotalAvgCost = SUMMARIZE(Items,Items[Item], "Cost", AvgCost)
    
    RETURN IF(HASONEVALUE(Items[Item]),AvgCost , SUMX(TotalAvgCost, [Cost] ) )
    

This gets me a number that looks like it might be right, but when you add them all up, it’s wrong.
Thanks,

Turn & Earn Example.pbix (182.5 KB)

Avg Cost Bal 2

=

//VAR AvgCost = AVERAGEX('Inventory Summary', 'Inventory Summary'[Cost Bal])

//VAR TotalAvgCost = SUMMARIZE(Items,Items[Item], "Cost", AvgCost)

var TestAvgCost = AVERAGEX('Inventory Summary', 'Inventory Summary'[Cost Bal])

RETURN IF(HASONEVALUE(Items[Item]),[TestAvgCost] , SUMX(VALUES(Items[Item]), [TestAvgCost]) )

Try the above

hmm, I don’t get the same results you do

Avg Cost Bal 2 = 

VAR TestAvgCost = AVERAGEX('Inventory Summary', 'Inventory Summary'[Cost Bal])

RETURN IF(HASONEVALUE(Items[Item]),TestAvgCost , SUMX(VALUES(Items[Item]), TestAvgCost) )

I don’t know why it did not work for you… I simplified the dax further and have attached the pbix file. pls have a look.

=
Avg Cost Bal 2 =
IF(HASONEVALUE(Items[Item]),[Avg Cost Bal] , SUMX(VALUES(Items[Item]), [Avg Cost Bal]))

Refer screen shot below and i have also updated your pbix file

Turn & Earn Example.pbix (173.7 KB)
[/quote]

1 Like

Yes that totally works. So weird that I’m having to separate it into two measures though.

Anyway, thanks so much for the help.