Average of Totals on Report

[Anonymous]

I have the following data records where AvgPrice is an added column in the dataset and QTYCWT is the weight of all similar products on the order:

Customer  Product  Order#   OrderTotal   QTYCWT  AvgPrice  
JJFoods     1,2,3        1203       $248.59        10.50       $23.68  
JJFoods     2,3,4        1205       $340.46        10.50       $32.42  
JJFoods     1,3           1256       $122.24          3.50       $34.93  
JJFoods     2,4           1288       $331.98          7.00       $47.43  

When I use "AvgPrice = CALCULATE((SUM(Query1[OrderTotal]))/(SUM(Query1[QTYCWT])))", it defaults to "Sum" which is adding all the AvgPrice's together. When I change it to "Average" of AvgPrice I get $34.61 when I roll up the total for "JJFoods" in a report. This PowerBI formula returns the average of the AvgPrice = 34.61, which makes sense. 

However, I would like to get the average of the totals per customer like below (1043.27/31.5 = 33.12 dollars per CWT)    
Customer   Product  OrderTotal   QTYCWT  AvgOfTotal  
JJFoods      1,2,3,4     $1,043.27     31.50       $33.120
 

What formula would I use so that AvgPrice calculates the correct average based on the total for the customer instead of the average of the averages for each line item (or each record)?

Thanks,

Kelly

[Greg_Deckler]

Check out the technique here, I believe it is relevant. Essentially, I think you will need to use a SUMMARIZE in your measure.

https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

[Anonymous]

Yes, I was thinking of using SummarizedColumns but wasn't sure how to structure the syntax. Everything I tried didn't work - gave syntax error. So then I tried using SUM on the columns but this just gave simple per line or per data record averages. Should I perform the sum as an added measure instead of an added column?

I think part of the answer might be in your link but not sure what the MIN calculation has to do with it.

The data I have is sales data for a commodity and each record includes seperate columns for customer, region (ship to zip code), size, color, grade/quality, weight sold, price received . Each record is per individual order and all items are sold by weight, but the basic commodity is the same. Trying to get average price of the total for the customer OR the size OR the color OR the grade using slicers. The slicers work great but my average price is incorrect - calculated Avg so far is not based on totals in the report summary. All of these are based on the total weight sold and prices recevied for those orders. We want to be able to slice based on the other attributes to determine where our marketing focus should be, or where are the best prices being found. On average which customer gives the best price per pount or which grade gives the best price, etc.

[Greg_Deckler]

The MIN was just the second aggregation, so I was interested in getting the MIN of the AVERAGES. In your case, it may be more simple, just create a table using SUMMARIZE and summarize by customer with the columns of OrderTotal and QTYCWT and a calculated column of [OrderTotal]/[QTYCWT]?

[Anonymous]

I've gotten this far with a Summarize Table:

AvPriceTable = Summarize(Query1, Query1[LineTotal], Query1[QTYCWT], "AvPrice", (Query1[LineTotal]/Query1[QTYCWT]))

Which returns the same average for every customer. Where in the syntax do I insert my customer field to have this compile or save values per customer? Customer in this case is "Query1[CustomerName]"

[Anonymous · Accepted solution]

I think I've solved it. I've created a measure called TtlSales=SUM(Query1[LineTotal]) and TtlCWT=SUM(Query1[QTYCWT]), which is total Weight. Then created a column called AvgPrice = TtlSales/TtlCWT. The average of this AvgPrice creates the actual average of the totals per customer.