DAX Average Price Group By Value

[zileng]

Hello,

How do I group total orders by order numbers?

For total orders more than 200, name order number "> USD 200"

For total orders less than 200, name order number "< USD 200"

Order Number	Product	Qty	Orders
000001	A	1	10
000001	B	10	30
000001	C	2	10
000001	D	5	60
000001	E	3	10
000002	A	4	55
000002	B	8	45
000002	C	5	30
000003	A	8	90
000003	B	60	100
000003	C	6	30
000003	D	2	60
000003	E	4	20
000004	A	4	200
000004	B	30	60
000004	C	2	7

After grouping, I need to create another table to separate order number "> USD 200" and "< USD 200" with average price of each product.

Product	< USD 200	> USD 200
A	Average Price	Average Price
B	Average Price	Average Price
C	Average Price	Average Price
D	Average Price	Average Price
E	Average Price	Average Price

[Pragati11 · Accepted solution]

Hi @zileng ,

Create the following measure to get total orders by ORDER NUMBER:

totalOrdersbyOrderNum =

CALCULATE(SUM( OrderData[Orders]),

FILTER(ALL(OrderData),

SUMX(

FILTER((OrderData), EARLIER( OrderData[Order Number] ) = OrderData[Order Number] ), OrderData[Orders] )

)

)

Now created a calculated column to categorise these totals:

OrderGroup1 =

IF(

[totalOrdersbyOrderNum] > 200, "> USD 200", "< USD 200"

)

Now just to check the calculations, move the above calculations to a table visual:

The calculations look right, so now create a matrix visualisation:

I don't see a PRICE column in your data therefore have moved total QTY to VALUES section. But you can move Average price here.

Thanks,

Pragati

[amitchandak]

@zileng , I do not see any amount or price column, but the calculation would be like this

order < 200 = AverageX(filter(summarize(Table,Table[product], "_1", sum(Table[Qty])), [_1]< 200),[_1])

order >= 200 = AverageX(filter(summarize(Table,Table[product], "_1", sum(Table[Qty])), [_1]>= 200),[_1])