Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
STS_Joshua
Helper II
Helper II

DAX 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:

 

ABCDEFGH
Sack 1.1Sack 1Storage SacksN012635850
Sack 1.2Sack 1Storage SacksN012635850
Sack 1.3Sack 1Storage SacksN012635850
Sack 1.4Sack 1Storage SacksN58512635850
Sack 1.5Sack 1Storage SacksY41512635850.612094
Sack 1.6Sack 1Storage SacksY26312635850.387906
Sack 2.1Sack 2Storage SacksN015634860
Sack 2.2Sack 2Storage SacksN015634860
Sack 2.3Sack 2Storage SacksN015634860
Sack 2.4Sack 2Storage SacksN48615634860
Sack 2.5Sack 2Storage SacksY64415634860.597957
Sack 2.6Sack 2Storage SacksY43315634860.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:

ABCDEFGH
Sack 1.1Sack 1Storage SacksN0126310710
Sack 1.2Sack 1Storage SacksN012631071 0
Sack 1.3Sack 1Storage SacksN012631071 0
Sack 1.4Sack 1Storage SacksN58512631071 0
Sack 1.5Sack 1Storage SacksY41512631071 0.612094
Sack 1.6Sack 1Storage SacksY26312631071 0.387906
Sack 2.1Sack 2Storage SacksN015631071 0
Sack 2.2Sack 2Storage SacksN015631071 0
Sack 2.3Sack 2Storage SacksN015631071 0
Sack 2.4Sack 2Storage SacksN48615631071 0
Sack 2.5Sack 2Storage SacksY64415631071 0.597957
Sack 2.6Sack 2Storage SacksY43315631071 0.402043

 

ABCDEFGH
Sack 1.1Sack 1Storage SacksN0126300
Sack 1.2Sack 1Storage SacksN0126300
Sack 1.3Sack 1Storage SacksN0126300
Sack 1.4Sack 1Storage SacksN58512635850
Sack 1.5Sack 1Storage SacksY415126300.612094
Sack 1.6Sack 1Storage SacksY263126300.387906
Sack 2.1Sack 2Storage SacksN0156300
Sack 2.2Sack 2Storage SacksN0156300
Sack 2.3Sack 2Storage SacksN0156300
Sack 2.4Sack 2Storage SacksN48615634860
Sack 2.5Sack 2Storage SacksY644156300.597957
Sack 2.6Sack 2Storage SacksY433156300.402043

 

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

1 ACCEPTED SOLUTION
v-kelly-msft
Community Support
Community 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:

 

Annotation 2020-02-25 122142.png

For the related .pbix file,pls click here.

 

Best Regards,
Kelly
 
Did I answer your question? Mark my post as a solution!

View solution in original post

4 REPLIES 4
v-kelly-msft
Community Support
Community 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:

 

Annotation 2020-02-25 122142.png

For the related .pbix file,pls click here.

 

Best Regards,
Kelly
 
Did I answer your question? Mark my post as a solution!
amitchandak
Super User
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])

Anonymous
Not applicable

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

 

Mansoorsadat1_0-1671308087783.png

 

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. 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors