Average Selling Price

[matef]

Hello all, i need to calculate the avrage selling price for multiple items, so the scenario as below

i have around 5 items, i need to get the fourmila as below

sum of net value for each item/sum of item quantites from the same item

all of that to be done on monthely basis so is there any tips and tricks help me in this request ? 

[v-yueyunzh-msft · Accepted solution]

Hi, @matef 

According to your description, You want to generate a table, grouped by month and item, and calculate the total profit, total, and average for each item. Right?

Here are the steps you can follow：

(1)This is my test data:

(2) We can click "New Column" in the original table to create a year and month column:

Month = YEAR('Sheet4'[Billing Date])*100 & MONTH('Sheet4'[Billing Date])

(3)We can click “New Table” and enter :

Table =
ADDCOLUMNS (
    SUMMARIZE (
        'Sheet4',
        'Sheet4'[Month],
        'Sheet4'[Short Description],
        "Sum of w/o tax", SUM ( 'Sheet4'[Value w/o tax] ),
        "Sum of quatity", SUM ( 'Sheet4'[Billed Quantity] )
    ),
    "Average per Month", DIVIDE ( [Sum of w/o tax], [Sum of quatity] )
)

(3)Then we can meet your need ,the result is as follows:

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

Best Regards,

Aniya Zhang

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

[matef]

Thnx Very Much i tried and its working fine

[Anonymous]

Hi, try this.

Create a Calculated Table:

Summarize ( TEST ; "Month Column" ; Month(TEST[Billing Date]);"Product Column" ; TEST[Short Description]; "Earnings" ; SUM(TEST[Value w/o tax]) ; "Quantity item per Month " ; SUM(TEST[Billed Quantity]);"Average per Month" ; [Earnings]/[Quantity item per Month])

[matef]

tried alot, but i think its not working fine with me, tried the same fourmila but its not working i do the summrize for both billing date and materials its coming correct but the summition of the values it not coming 

Table = SUMMARIZE(TEST, "Month Column", Month(TEST[Billing Date].[Month], "Product Column", TEST[Short Description], "Earnings", (SUM(TEST[Value w/o tax])), "Quantity item per Month ", (SUM(TEST[Billed Quantity])), "Average per Month", [Earnings]/[Quantity item per Month])

i used the above one, but also there is no result

this is the error which i recived

and if i use the same code excatly 

i got this one 

[Anonymous]

in your second try use commas instead semi colon

[Anonymous]

Do you still trying?

[matef]

yes already tried with changing some fourmilas and its fixed, lof of thanx for your support

[matef]

Data Example For Refrence