topic Re: Average Selling Price in DAX Commands and Tips

Average Selling Price

Anonymous — Sun, 11 Sep 2022 20:03:26 GMT

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 ?

Re: Average Selling Price

Anonymous — Sun, 11 Sep 2022 20:06:04 GMT

Data Example For Refrence

Re: Average Selling Price

Anonymous — Sun, 11 Sep 2022 20:32:48 GMT

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])

Re: Average Selling Price

Anonymous — Mon, 12 Sep 2022 07:25:34 GMT

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

Re: Average Selling Price

v-yueyunzh-msft — Tue, 13 Sep 2022 07:26:25 GMT

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

Re: Average Selling Price

Anonymous — Thu, 15 Sep 2022 00:28:22 GMT

Do you still trying?

Re: Average Selling Price

Anonymous — Thu, 15 Sep 2022 00:48:38 GMT

in your second try use commas instead semi colon

Re: Average Selling Price

Anonymous — Thu, 15 Sep 2022 09:48:55 GMT

Thnx Very Much i tried and its working fine

Re: Average Selling Price

Anonymous — Thu, 15 Sep 2022 09:49:41 GMT

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