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

[BudMan512]

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=10...

Thanks,

Bud

[v-rzhou-msft]

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.

[BudMan512]

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

[BudMan512 · Accepted solution]

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

[amitchandak]

@BudMan512 , for that you need a column

Price column =

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

[BudMan512]

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

[BudMan512]

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.