Total sum by unique user

[chucknorris]

Hello everybody,

I want to calculate total quantity value at a certain warehouse id (that can be selected via power bi GUI).
The value should be shown at first occurence of a unique customerNumber/user-id, which could also be calculated via minimal order date per user.

I could do it easily on database side via SQL queries (see totalQuantityAtWarehouseId as a correct result). Unfortunately, it doesn't consider the filter context (warehouseId, orderDate), which is why I want to create a measure to calculate it.

Unfortunately, my currently best solution is the following

Measure = CALCULATE(SUMX (Extended_User_Orders, Extended_User_Orders[correctlySortedOrderQuantity]), Extended_User_Orders[status]="SUCCESS")

and doesn't sum by user, only row by row.

warehouseId	userId	status	orderNumber	customerNumber	orderDate	totalQuantityAtWarehouseId	Measure	correctlySortedOrderQuantity	orderBy
625FF6B0BF8EB2718D65A2A1	0556585d-f076-4294-be9d-969288e3f31f	CANCELLED	6760001532-1	6760001532	26.09.2022 07:01			1	1
625FF6B0BF8EB2718D65A2A1	0556585d-f076-4294-be9d-969288e3f31f	CANCELLED	6760001532-1	6760001532	26.09.2022 07:01			-1	100
625FF6B0BF8EB2718D65A2A1	0556585d-f076-4294-be9d-969288e3f31f	SUCCESS	6760001532-2	6760001532	26.09.2022 07:02	2	1	1	1
625FF6B0BF8EB2718D65A2A1	0556585d-f076-4294-be9d-969288e3f31f	SUCCESS	6760001532-3	6760001532	26.09.2022 10:57		1	1	1
625FF6B0BF8EB2718D65A2A1	105958be-7122-4a8e-8682-eded4b6e9b94	SUCCESS	6760001606-19	6760001606	11.10.2022 07:02		1	1	1
625FF6B0BF8EB2718D65A2A1	105958be-7122-4a8e-8682-eded4b6e9b94	SUCCESS	6760001606-2	6760001606	28.09.2022 07:00	5	1	1	1
625FF6B0BF8EB2718D65A2A1	105958be-7122-4a8e-8682-eded4b6e9b94	SUCCESS	6760001606-4	6760001606	29.09.2022 07:01		1	1	1
625FF6B0BF8EB2718D65A2A1	105958be-7122-4a8e-8682-eded4b6e9b94	SUCCESS	6760001606-6	6760001606	30.09.2022 07:02		1	1	1
625FF6B0BF8EB2718D65A2A1	105958be-7122-4a8e-8682-eded4b6e9b94	SUCCESS	6760001606-8	6760001606	01.10.2022 07:01		1	1	1

I'm aware that I need to group by user-id and then calculate the sum, but it only works if I create another table (which as a result is unaware of the filter context)

Table = 
SUMMARIZE (
    ALLSELECTED(Extended_User_Orders),
    Extended_User_Orders[customerNumber],
    "Sales", SUM ( Extended_User_Orders[correctlySortedOrderQuantity] )
)

Could you please help me with this problem? I'm new to power bi and DAX measures are a bit confusing to me.
Thank you in advance.

[v-mengzhu-msft · Accepted solution]

Hi @chucknorris ,

If you want to group summation by userid, you can refer to a formula like this, which applies to group summation, and modify it accordingly according to your actual situation.

Measure = CALCULATE(SUM('Table (2)'[Value]),FILTER(ALL('Table (2)'),'Table (2)'[Attribute]=SELECTEDVALUE('Table (2)'[Attribute])))

You can see that it is grouped by attribute. In your case, just replace it with "user id ".

Best regards,

Community Support Team Selina zhu

[chucknorris]

Hi Selina,

thank you for your answer. 
This solution with measure 

Measure2 = CALCULATE(SUM(testdata_powerbi[correctlySortedOrderQuantity]),FILTER(ALL(testdata_powerbi),testdata_powerbi[userId]=SELECTEDVALUE(testdata_powerbi[userId])))

works! 🙂
Kind regards, 
Chuck Norris