topic Re: Calculate Average Price by row for aggregated Qty and Sales for various products. in DAX Commands and Tips

Calculate Average Price by row for aggregated Qty and Sales for various products.

BudMan512 — Wed, 02 Nov 2022 22:10:55 GMT

I have a list of customer purchaces of various fuels for which I have summed the Qty and Sales by Category and Branch. I would like to find the average price of each product. I have attached a spreadsheet with Test Data and also including my desired output.

Here is the DAX I used to sum the QTY. The Inv_Multiplier is a conversion factor for various fuels and can be ignored:

Total gallons = CALCULATE(SUMX(History, History[QTY] * History[INV_MULTIPLIER]))

Here is the DAX for Total Sales:

Total Sales = CALCULATE(SUMX(History, History[Sales]))

And here is the DAX for Avg Sell Price, which is driving me crazy.

Avg Sell Price = DIVIDE(History[Total Sales],History[total Gallons],0)

The problem is when I set my Avg Sell Price 'Summerization' to 'Sum', it sums up all the individual transaction's average prices, not good.

If I set the Avg Sell Price 'Summerization' to 'Don't summarize', it gives me the Average Sell Price for each individual transaction, that's worse.

What I hope to find is DAX that will allow me to calculate the the Avg Sell Price for each row. Desired output is below .

Here is my Input History table:

Here is a link to the spreadsheet. Let me know if this doesn't work as I have not tried this before.

https://docs.google.com/spreadsheets/d/1F3dCbXLF1X2eu96kKRVmVtHDjKAwRDNZ/edit?usp=share_link&ouid=108795197017177326252&rtpof=true&sd=true

Thanks,

Bud

Re: Calculate Average Price by row for aggregated Qty and Sales for various products.

amitchandak — Thu, 03 Nov 2022 02:57:27 GMT

BudMan512 , for that you need a column

Price column =

divide(History[QTY] * History[INV_MULTIPLIER] ,History[Sales])

Re: Calculate Average Price by row for aggregated Qty and Sales for various products.

Anonymous — Thu, 03 Nov 2022 07:44:18 GMT

Hi BudMan512 ,

Here I suggest you to try to create a measure instead of a calculated column.

[Total gallons] and [Total Sales] should both be measure as well. Then you can show your result in a table visual.

Avg Sell Price = DIVIDE(History[Total Sales],History[total Gallons],0)

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Calculate Average Price by row for aggregated Qty and Sales for various products.

BudMan512 — Thu, 03 Nov 2022 13:02:44 GMT

Hi Rico, thanks for the reply.

What you have prescribed is what I have, all DAX is in measures, here they are:

Total gallons = CALCULATE(SUMX(History, History[QTY] * History[INV_MULTIPLIER])

Total Sales = CALCULATE(SUMX(History, History[Sales]))

Avg Sell Price = DIVIDE(History[Total Sales],History[total Gallons],0)

Yet my result is different. When I Sum the Avg Sell Price measure, the table appears the way I want, but it appears to be summing the Avg of individual transactions.

When I choose not to sum. it then displays every individual transaction, rather than an aggregated table.

Is there something different causing my problem?

I appreciatae the help.

Bud

Re: Calculate Average Price by row for aggregated Qty and Sales for various products.

BudMan512 — Thu, 03 Nov 2022 13:15:45 GMT

Thanks for the reply amitchanda. I tried to create a Price column but it produced the same problem.

These are all measures.

Total gallons = CALCULATE(SUMX(History, History[QTY] * History[INV_MULTIPLIER])

Total Sales = CALCULATE(SUMX(History, History[Sales]))

Avg Sell Price = DIVIDE(History[Total Sales],History[total Gallons],0)

Here is the calculated column I tried.

Price = DIVIDE(History[QTY] * History[INV_MULTIPLIER], History[TOTAL_PRICE])

This is what I am getting for results, with my measure and your Price column.

Thanks for your time.

Re: Calculate Average Price by row for aggregated Qty and Sales for various products.

BudMan512 — Thu, 03 Nov 2022 19:30:25 GMT

Hi Rico, my apologies, my Avg Sell Price was not a measure but a calculated column. I couldn't get an acurate value thou until I used this DAX:

Avg Sell Price = CALCULATE(DIVIDE(History[Total Sales],History[total Gallons],0))

Thanks for helping,

Bud

Re: Calculate Average Price by row for aggregated Qty and Sales for various products.

BudMan512 — Thu, 03 Nov 2022 19:34:33 GMT

Hi amitchandak, Here is the measure that ended up working for me:

Avg Sell Price = CALCULATE(DIVIDE(History[Total Sales],History[total Gallons],0))

I tried everything else under the sun but this works.

Thanks for answering by question.

Regards,

Bud