Grouping

[Anonymous]

Hello,
So I need help with calculation.

Table has many columns. One of them is order number (one number is repeted but has different data). Just example:

Order NR	Client	Price
214	a	30
214	b	10
215	c	20
216	d	10
216	e	40

Average price = 22
Average price for Order nr = (30+10)+20+(10+40) / 3 =36.6

 
Price is a calculated column.
I need to know what is the average Price for the ORDER NUMBER 
Thanks for help
Aleks

[johnt75 · Accepted solution]

Try

Avg Price per order number =
AVERAGEX (
    ADDCOLUMNS (
        VALUES ( 'Table'[Order number] ),
        "@val", CALCULATE ( SUM ( 'Table'[Price] ) )
    ),
    [@val]
)

[Anonymous · Accepted solution]

Hi  @Anonymous ,

Here are the steps you can follow：

Measure:

Measure =
var _sum=SUMX(ALL('Table'),[Price])
var _count=CALCULATE(DISTINCTCOUNT('Table'[Order NR]),ALL('Table'))
return
DIVIDE(_sum,_count)

Calculated column:

Column =
var _sum=SUMX(ALL('Table'),[Price])
var _count=DISTINCTCOUNT('Table'[Order NR])
return
DIVIDE(_sum,_count)

Result:

Best Regards,

Liu Yang

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

[johnt75 · Accepted solution]

Try

Avg Price per order number =
AVERAGEX (
    ADDCOLUMNS (
        VALUES ( 'Table'[Order number] ),
        "@val", CALCULATE ( SUM ( 'Table'[Price] ) )
    ),
    [@val]
)