Forum Discussion
Grouping
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
Try
Avg Price per order number = AVERAGEX ( ADDCOLUMNS ( VALUES ( 'Table'[Order number] ), "@val", CALCULATE ( SUM ( 'Table'[Price] ) ) ), [@val] )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
2 Replies
Try
Avg Price per order number = AVERAGEX ( ADDCOLUMNS ( VALUES ( 'Table'[Order number] ), "@val", CALCULATE ( SUM ( 'Table'[Price] ) ) ), [@val] )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