Forum Discussion
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 multiple columns and rows) where I want to add a measure, in column D, where I would sumif sales based on employee.
| A | B | C | D | |
| 1 | Emp | Prod | Sales | SalesXEmp |
| 2 | ||||
| 3 | Jones | Apple | 100 | 125 |
| 4 | Smith | Apple | 150 | 250 |
| 5 | Smith | Kiwi | 100 | 250 |
| 6 | Lake | Kiwi | 75 | 75 |
| 7 | Jones | Dates | 25 | 125 |
In Excel, my sumif formula (in cell D3) is simple = =SUMIF($A:$A,A3,$C:$C).
How do I recreate this sumif based on the value on a column (or aggregate using group by) in Power BI using a measure?
Thank you.
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))
8 Replies
- HotChilli
Community Champion
Write a measure :
MSales = CALCULATE(SUM(TableD[Sales]), ALLEXCEPT(TableD, TableD[Emp])) - amitchandak
Super User
Claudine ,
As a new column
SalesXEmp = sumx(filter(Table, [Emp]=earlier([Emp])),[Sales])Sales
As a new measure
SalesXEmp = calculate(sum(Table[Sales]),allexcept(Table, Table[Emp]))Hello Amit,
Thank you for your reply. ALLEXCEPT was one of the solutions I tried earlier, but it continued to alter my result set.
I just did a trial and error and it seems that they have isolated the condition in which the ALLEXCEPT works and don't work. I have 2 tables joined together through ITEMID. If I exclude the column from the child table, THE ALLEXCEPT produces the expected results, however, when I include it, which is what I need, alters the result set and seems to have increased the number of records as if it were doing Cartesian union.
BTW, when I exclude the measure, the resulting set, with the 2 tables looks good. So it seems to be happening when I have both the measure AND the column from the secondary table included.
Any ideas?
Thank you
C
Hello Amit ( amitchandak ), hi HotChilli ( HotChilli ),
Thanks for your responses. The ALLEXCEPT was one of the solutions I previously tried but I couldn't get the appropriate result set.
After doing some trial and error, I was able to isolate the conditions in which the ALLEXCEPT would work and not work. I have 2 tables joined via ITEMID. When adding the measure, if I exclude the column from the secondary table, the measure works as it should. However, when I include it, which is what I need, it's throwing off my result set and my records seems to grow exponentially, as though it performed a cartesian join.
BTW, if I exclude the measure, the joined tables looks good in my result set. It's acting up when both the measure and the secondary table's column(s) are included.
Any ideas?
Thanks,
C
- HotChilli
Can you provide 2 sample tables (doesn't have to be real data ) and what your desired outcome is please?
