Forum Discussion
Solved
Summing a Column Grouped by Value in Another Column
Hello, I'm trying to do a simple sumif (simple in Excel that is) but can't seem to make it work Power BI If I have the sample table below (simplified for this sample but actual data has mult...
Thanks for the data.
Adding the dimension table to the allexcept clause should get the correct result.
Something like
CALCULATE(SUM(TableA[ONHANDQTY]), ALLEXCEPT(TableA, TableA[ITEMID], TableB))
Can you provide 2 sample tables (doesn't have to be real data ) and what your desired outcome is please?
Hello HotChilli ,
Can't seem to attach even a txt file...hopefully, the tables below suffices...
Table 1:
| WAREHOUSE | ITEMID | ONHANDQTY | SUPPLYDATE | SUPPLYDAYS |
| HAYWD | M10019 | 300 | ||
| MINN | M10019 | 800 | ||
| PENHP | M10019 | 584 | ||
| DALLA | M10019 | 600 | 1/11/2021 0:00 | 273 |
| HAYWD | M10022 | 300 | ||
| MINN | M10022 | 3000 | ||
| PENHP | M10022 | 589 | ||
| DALLA | M10022 | 2300 | 12/31/2020 0:00 | 262 |
| HAYWD | M10030 | 4000 | ||
| MINN | M10030 | 300 | ||
| PENHP | M10030 | 6032 | ||
| DALLA | M10030 | 30450 | 12/15/2020 0:00 | 245 |
| HAYWD | M10032 | 700 | ||
| MINN | M10032 | 400 | ||
| DALLA | M10032 | 2500 | 7/4/2026 0:00 | 2273 |
| PENHP | M10032 | 339 | 6/4/2027 0:00 | 2608 |
Table 2:
| ITEMID | ITEMNAME |
| M10019 | MH3.5AUIN1/4-1/4AB |
| M10022 | MH3.5AUIN1/4-1/4BB |
| M10030 | MH3.5AUIN1/4-1/4CM |
| M10032 | MH3.5AUIN1/4-1/4DB |
Basically, the tables are joined and I've included Item Name (from Table 2) and the last column, QtyTotal, is the measure I want to get.
| WAREHOUSE | ITEMID | ITEMNAME | ONHANDQTY | SUPPLYDATE | SUPPLYDAYS | QTYTOTAL |
| HAYWD | M10019 | MH3.5AUIN1/4-1/4AB | 300 | 2284 | ||
| MINN | M10019 | MH3.5AUIN1/4-1/4AB | 800 | 2284 | ||
| PENHP | M10019 | MH3.5AUIN1/4-1/4AB | 584 | 2284 | ||
| DALLA | M10019 | MH3.5AUIN1/4-1/4AB | 600 | 1/11/2021 0:00 | 273 | 2284 |
| HAYWD | M10022 | MH3.5AUIN1/4-1/4BB | 300 | 6189 | ||
| MINN | M10022 | MH3.5AUIN1/4-1/4BB | 3000 | 6189 | ||
| PENHP | M10022 | MH3.5AUIN1/4-1/4BB | 589 | 6189 | ||
| DALLA | M10022 | MH3.5AUIN1/4-1/4BB | 2300 | 12/31/2020 0:00 | 262 | 6189 |
| HAYWD | M10030 | MH3.5AUIN1/4-1/4CM | 4000 | 40782 | ||
| MINN | M10030 | MH3.5AUIN1/4-1/4CM | 300 | 40782 | ||
| PENHP | M10030 | MH3.5AUIN1/4-1/4CM | 6032 | 40782 | ||
| DALLA | M10030 | MH3.5AUIN1/4-1/4CM | 30450 | 12/15/2020 0:00 | 245 | 40782 |
| HAYWD | M10032 | MH3.5AUIN1/4-1/4DB | 700 | 3939 | ||
| MINN | M10032 | MH3.5AUIN1/4-1/4DB | 400 | 3939 | ||
| DALLA | M10032 | MH3.5AUIN1/4-1/4DB | 2500 | 7/4/2026 0:00 | 2273 | 3939 |
| PENHP | M10032 | MH3.5AUIN1/4-1/4DB | 339 | 6/4/2027 0:00 | 2608 | 3939 |
Regards,
C