Forum Discussion
Total sum by unique user
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.
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
2 Replies
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
Hi Selina,
thank you for your answer.
This solution with measureMeasure2 = CALCULATE(SUM(testdata_powerbi[correctlySortedOrderQuantity]),FILTER(ALL(testdata_powerbi),testdata_powerbi[userId]=SELECTEDVALUE(testdata_powerbi[userId])))works! 🙂
Kind regards,
Chuck Norris
