Summing a Column Grouped by Value in Another Column

[Claudine]

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.

[amitchandak]

@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]))

[Claudine]

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?

[Claudine]

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

[HotChilli · Accepted solution]

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))

[Claudine]

Thank you very much @HotChilli !

[Claudine]

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

[HotChilli]

Write a measure :

MSales = CALCULATE(SUM(TableD[Sales]), ALLEXCEPT(TableD, TableD[Emp]))