Average Selling Price - Criteria from Multiple Columns

[cisenberg]

I need to calculate the average selling price of an item in a Sales table. This would normally be as simple as creating a calculated column using:

DIVIDE(
     SUM(Sales[SalesAmount]),
     SUM(Sales[SalesQty])
) 

but my Sales table has additional data, for example the [CustomerID], [SalesRegion], and [SalesType]. I need to find the average selling price for each item identified by these features. For example, I need the average price for Item A purchased by Customer A in an Online Sale, which is different from Item A purchased by Customer A in an In-Store sale.

Originally, I used something like this:

ASP by Item = 
    VAR PriorYear = YEAR(Sales[Invoice Date]) - 1
    VAR CurrentItem = RELATED(Item[ItemID])
    VAR CurrentCustomer = RELATED(Customer[CustID])
    RETURN
    CALCULATE(
        DIVIDE(SUM(Sales[SalesAmount]), SUM(Sales[SalesQty])),
        FILTER(
            Sales,
            YEAR(Sales[Invoice Date]) = PriorYear
        ),
        FILTER(
            Sales,
            RELATED(Item[ItemID]) = CurrentItem
        ),
        FILTER(
            Sales,
            RELATED(Customer[CustID]) = CurrentCustomer
        )
    )

My actual use case has more filters, and I end up running out of memory. My current solution is to use SUMMARIZECOLUMNS to make a table with the ASP, and then I created a key column by concatenating the relevant columns together in this table and my Sales table. This works, but the process is quite slow.

My Sales table has about 3M records, with he Customer and Item tables being in the tens of thousands of records range.

Is there a more efficient way to "roll up" an average by multiple columns?

[v-juanli-msft]

Hi @cisenberg 

Is this problem sloved?

If not, please let me know.

If it is sloved, could you kindly accept useful method as a solution so that others may find the solution quickly?

Best Regards

Maggie

[Ashish_Mathur]

Hi,

DIVIDE(
     SUM(Sales[SalesAmount]),
     SUM(Sales[SalesQty])
) 

should be written as a measure (not as a calculated column)

[cisenberg]

For each sales transaction (i. e., row in Sales), I need the average selling price for a particular combination of item, customer, sales region, sales type, etc.. How would I construct a measure for this?

[Ashish_Mathur]

Hi,

I do not understand.  Share some data and show the expected result.

[cisenberg]

Sure. To use a simplified example, let's say we have these sales transactions:

Date        Sales ID       Sales Amount             Sales Qty          Item ID     Customer ID     Sale Type
1/1/2017    0001           $5.00                     1                  A001        001             I
2/1/2017    0002           $3.00                     2                  A001        002             I
3/1/2017    0003           $3.00                     2                  A001        001             I
4/1/2017    0004           $3.00                     1                  A001        001             T
1/1/2018    0005           $5.00                     1                  A001        001             I
2/1/2018    0006           $3.00                     2                  A001        002             I
3/1/2018    0007           $4.00                     1                  A001        001             T
4/1/2018    0008           $3.00                     2                  A001        002             T

The output of the measure will be this:

Average Selling Price Previous Year
(Blank)
(Blank)
(Blank)
(Blank)
$2.67
$1.50
$3.00
(Blank)

For each sales transaction, I need to get the average selling price that is uniquely identified by year, item, customer, and sales type. Since this measure calculates the ASP for the previous year, I need to make sure it returns a blank if there's no sales data for the previous year.

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[cisenberg]

Hi,

I realize that you've provided a perfectly good solution to the problem I set before you, but I actually have a different problem. I thought that solving the ASP question would bridge me to a full solution, but it didn't. My apologies for not being clearer.

I'm trying to calculate "price attainment." This is a value associated with each order line that's calculated like this (note the need for ASP):

(Sales Amount - Average Selling Price from Prior Year by Customer, Item, Sales Type) * Sales Quantity

This value is blank if there is no sales data for that particular combination of Customer, Item, and Sales Type in the previous year. Again, these 3 fields are a simplification of my real data, which has an additional 4 fields that identify the criteria for "Average Selling Price."

This calculation is then used in various visuals, mostly bar and line charts, which track Price Attainment by Sales Region, by Sales Date, by Product Type, etc. I also include slicers that allow you to cross-filter; a chart with Sales Date on the x-axis, for example, will have slicers for Sales Region, Product Type, and Sales Type.

Finally, each canvas also has two Card visuals for Total Price Attainment (for everything on that canvas in the current filter context), as well as Price Attainment %, which is calculated like this:

Price Attainment / Sales Amount (records with a non-blank Price Attainment value)

Essentially, we want to know Price Attainment as a % of Sales, but only those sales that have a Price Attainment value.