DAX SUMIFS Equivalent

[STS_Joshua]

HI All,

I'm looking for how to do something fairly simple in Excel in power BI.

I can get this working in Excel easily, I have a table of items and their quantities sold. I want a column showing the total for each category according to a criteria. Here is the table in excel:

A	B	C	D	E	F	G	H
Sack 1.1	Sack 1	Storage Sacks	N	0	1263	585	0
Sack 1.2	Sack 1	Storage Sacks	N	0	1263	585	0
Sack 1.3	Sack 1	Storage Sacks	N	0	1263	585	0
Sack 1.4	Sack 1	Storage Sacks	N	585	1263	585	0
Sack 1.5	Sack 1	Storage Sacks	Y	415	1263	585	0.612094
Sack 1.6	Sack 1	Storage Sacks	Y	263	1263	585	0.387906
Sack 2.1	Sack 2	Storage Sacks	N	0	1563	486	0
Sack 2.2	Sack 2	Storage Sacks	N	0	1563	486	0
Sack 2.3	Sack 2	Storage Sacks	N	0	1563	486	0
Sack 2.4	Sack 2	Storage Sacks	N	486	1563	486	0
Sack 2.5	Sack 2	Storage Sacks	Y	644	1563	486	0.597957
Sack 2.6	Sack 2	Storage Sacks	Y	433	1563	486	0.402043

Column A is the Item, B is the Item group, C is the category, D is active or not, E is total sales by item, F is total sales for the group, G is sum of sales in the group for inactive product, H is the percentage of sales for active items/(total groupsales - total group sales of inactive product)

In Excel it is a simple =SUMIFS(E:E,B:B,B#,D:D,"N")

In power BI I can calculate a column that gives me a calculated total of all item sales for inactive items, but not filtered down to the item group. OR I can only get a column showing item sales for inactive product. Basically the two tables below:

A	B	C	D	E	F	G	H
Sack 1.1	Sack 1	Storage Sacks	N	0	1263	1071	0
Sack 1.2	Sack 1	Storage Sacks	N	0	1263	1071	0
Sack 1.3	Sack 1	Storage Sacks	N	0	1263	1071	0
Sack 1.4	Sack 1	Storage Sacks	N	585	1263	1071	0
Sack 1.5	Sack 1	Storage Sacks	Y	415	1263	1071	0.612094
Sack 1.6	Sack 1	Storage Sacks	Y	263	1263	1071	0.387906
Sack 2.1	Sack 2	Storage Sacks	N	0	1563	1071	0
Sack 2.2	Sack 2	Storage Sacks	N	0	1563	1071	0
Sack 2.3	Sack 2	Storage Sacks	N	0	1563	1071	0
Sack 2.4	Sack 2	Storage Sacks	N	486	1563	1071	0
Sack 2.5	Sack 2	Storage Sacks	Y	644	1563	1071	0.597957
Sack 2.6	Sack 2	Storage Sacks	Y	433	1563	1071	0.402043

A	B	C	D	E	F	G	H
Sack 1.1	Sack 1	Storage Sacks	N	0	1263	0	0
Sack 1.2	Sack 1	Storage Sacks	N	0	1263	0	0
Sack 1.3	Sack 1	Storage Sacks	N	0	1263	0	0
Sack 1.4	Sack 1	Storage Sacks	N	585	1263	585	0
Sack 1.5	Sack 1	Storage Sacks	Y	415	1263	0	0.612094
Sack 1.6	Sack 1	Storage Sacks	Y	263	1263	0	0.387906
Sack 2.1	Sack 2	Storage Sacks	N	0	1563	0	0
Sack 2.2	Sack 2	Storage Sacks	N	0	1563	0	0
Sack 2.3	Sack 2	Storage Sacks	N	0	1563	0	0
Sack 2.4	Sack 2	Storage Sacks	N	486	1563	486	0
Sack 2.5	Sack 2	Storage Sacks	Y	644	1563	0	0.597957
Sack 2.6	Sack 2	Storage Sacks	Y	433	1563	0	0.402043

How do I go about calculating Column G in the first table in DAX?

[v-kelly-msft · Accepted solution]

Hi @STS_Joshua ,

You need a measure as below:

G = CALCULATE(SUM('Table'[E]),FILTER(ALLSELECTED('Table'),'Table'[D]="N"))

And you will see:

For the related .pbix file,pls click here.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[amitchandak]

Power Bi You have to think in term of Column not cell by cell

You can refer SUMX

https://docs.microsoft.com/en-us/dax/sumx-function-dax

This is how it works, not what you need

SUMX(Filter(Table,Table[D]="N"),Table[E])

[Anonymous]

Is there any way I can replicate the following in DAX. The following is from an Excel table:

[STS_Joshua]

That still gives me a column giving the total for all rows marked "N" in Column D. I need a total of column E for all rows with "N" in Column D where the Rows in Column B are the same.