Forum Discussion
Helper IIDAX SUMIFS Equivalent
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?
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,
KellyDid I answer your question? Mark my post as a solution!
4 Replies
v-kelly-msftCommunity Support
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,
KellyDid I answer your question? Mark my post as a solution!- amitchandak
Super User
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])
STS_JoshuaThat 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.
