Forum Discussion
Average Selling Price
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 ?
Hi, Anonymous
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
8 Replies
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])
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
- v-yueyunzh-msft
Community Support
Hi, Anonymous
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