Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- SUMS WITH MULTIPLE CRITERIA

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

moeconsult

Helper V

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-29-2020
09:54 PM

I need help with the DAX form of Excel formulas below as I tried and without luck and could use some help please:

F2FA -SUMIFS(B:B,A:A,A2,C:C,"Att",D:D,"<>on pp")

In SUMIFS (B:B,A:A,A2,C:C,"Att",D:D, in pp")

F2FP -SUMIFS(B:B,A:A,A2,C:C,"Pen",D:D,"<>on pp")

Please see below my sample and output data:

A B C D

Id | Hours | State | Type | F2FA | In | F2FP |

A | 5 | Att | Gap | 40 | 0 | 0 |

A | 5 | Att | Face | 40 | 0 | 0 |

A | 5 | Att | Face | 40 | 0 | 0 |

A | 5 | Att | Face | 40 | 0 | 0 |

A | 5 | Att | Gap | 40 | 0 | 0 |

A | 5 | Att | Face | 40 | 0 | 0 |

A | 5 | Att | Gap | 40 | 0 | 0 |

A | 5 | Att | Gap | 40 | 0 | 0 |

B | 5 | Att | Face | 120 | 0 | 0 |

B | 5 | Att | Gap | 120 | 0 | 0 |

B | 5 | Att | Face | 120 | 0 | 0 |

B | 5 | Att | Gap | 120 | 0 | 0 |

B | 5 | Att | Face | 120 | 0 | 0 |

B | 5 | Att | Face | 120 | 0 | 0 |

B | 5 | Att | Face | 120 | 0 | 0 |

B | 5 | Att | Gap | 120 | 0 | 0 |

B | 5 | Att | Face | 120 | 0 | 0 |

B | 5 | Att | Face | 120 | 0 | 0 |

B | 5 | Att | Face | 120 | 0 | 0 |

B | 5 | Att | Gap | 120 | 0 | 0 |

B | 5 | Att | Face | 120 | 0 | 0 |

B | 5 | Att | Gap | 120 | 0 | 0 |

B | 5 | Att | Face | 120 | 0 | 0 |

B | 5 | Att | Gap | 120 | 0 | 0 |

B | 5 | Att | Face | 120 | 0 | 0 |

B | 5 | Att | Face | 120 | 0 | 0 |

B | 5 | Att | Gap | 120 | 0 | 0 |

B | 5 | Att | Gap | 120 | 0 | 0 |

B | 5 | Att | Gap | 120 | 0 | 0 |

B | 5 | Att | Gap | 120 | 0 | 0 |

B | 5 | Att | Gap | 120 | 0 | 0 |

B | 5 | Att | Gap | 120 | 0 | 0 |

C | 2.5 | Pen | Rem | 25 | 0 | 5 |

C | 2.5 | Pen | Rem | 25 | 0 | 5 |

C | 5 | Att | A | 25 | 0 | 5 |

C | 5 | Att | Gap | 25 | 0 | 5 |

C | 5 | Att | Gap | 25 | 0 | 5 |

C | 5 | Att | Gap | 25 | 0 | 5 |

C | 5 | Att | Gap | 25 | 0 | 5 |

D | 3 | Pen | Rem | 10 | 0 | 6 |

D | 3 | Pen | Rem | 10 | 0 | 6 |

D | 5 | Att | Gap | 10 | 0 | 6 |

D | 5 | Att | Gap | 10 | 0 | 6 |

E | 2.5 | Pen | Rem | 10 | 0 | 15.5 |

E | 2.5 | Pen | Rem | 10 | 0 | 15.5 |

E | 2.5 | Pen | Rem | 10 | 0 | 15.5 |

E | 3 | Pen | Rem | 10 | 0 | 15.5 |

E | 2.5 | Pen | Rem | 10 | 0 | 15.5 |

E | 2.5 | Pen | Rem | 10 | 0 | 15.5 |

E | 5 | Att | Gap | 10 | 0 | 15.5 |

E | 5 | Att | Gap | 10 | 0 | 15.5 |

F | 3 | Pen | Rem | 30 | 0 | 6 |

F | 3 | Pen | Rem | 30 | 0 | 6 |

F | 5 | Att | Gap | 30 | 0 | 6 |

F | 5 | Att | Gap | 30 | 0 | 6 |

F | 5 | Att | Gap | 30 | 0 | 6 |

F | 5 | Att | Gap | 30 | 0 | 6 |

F | 5 | Att | Gap | 30 | 0 | 6 |

F | 5 | Att | Gap | 30 | 0 | 6 |

G | 2.5 | Pen | Rem | 25 | 0 | 5 |

G | 2.5 | Pen | Rem | 25 | 0 | 5 |

G | 5 | Att | A | 25 | 0 | 5 |

G | 5 | Att | Gap | 25 | 0 | 5 |

G | 5 | Att | Gap | 25 | 0 | 5 |

G | 5 | Att | Gap | 25 | 0 | 5 |

G | 5 | Att | Gap | 25 | 0 | 5 |

H | 2 | Pen | Rem | 0 | 0 | 2 |

I | 3 | Pen | Rem | 80 | 0 | 6 |

I | 3 | Pen | Rem | 80 | 0 | 6 |

I | 5 | Att | Gap | 80 | 0 | 6 |

I | 5 | Att | Gap | 80 | 0 | 6 |

I | 5 | Att | Gap | 80 | 0 | 6 |

I | 5 | Att | Gap | 80 | 0 | 6 |

I | 5 | Att | Gap | 80 | 0 | 6 |

I | 5 | Att | Gap | 80 | 0 | 6 |

I | 5 | Att | Gap | 80 | 0 | 6 |

I | 5 | Att | Gap | 80 | 0 | 6 |

I | 5 | Att | Gap | 80 | 0 | 6 |

I | 5 | Att | Gap | 80 | 0 | 6 |

I | 5 | Att | Gap | 80 | 0 | 6 |

I | 5 | Att | Gap | 80 | 0 | 6 |

I | 5 | Att | Gap | 80 | 0 | 6 |

I | 5 | Att | Gap | 80 | 0 | 6 |

I | 5 | Att | Gap | 80 | 0 | 6 |

I | 5 | Att | Gap | 80 | 0 | 6 |

J | 2.5 | Pen | Rem | 0 | 0 | 10 |

J | 2.5 | Pen | Rem | 0 | 0 | 10 |

J | 2.5 | Pen | Rem | 0 | 0 | 10 |

J | 2.5 | Pen | Rem | 0 | 0 | 10 |

K | 2.5 | Pen | Rem | 0 | 0 | 10 |

K | 2.5 | Pen | Rem | 0 | 0 | 10 |

K | 2.5 | Pen | Rem | 0 | 0 | 10 |

K | 2.5 | Pen | Rem | 0 | 0 | 10 |

L | 3 | Pen | Rem | 0 | 0 | 6 |

L | 3 | Pen | Rem | 0 | 0 | 6 |

M | 5 | Pen | Gap | 0 | 0 | 10 |

M | 5 | Pen | Gap | 0 | 0 | 10 |

N | 5 | Att | Gap | 40 | 0 | 0 |

N | 5 | Att | Gap | 40 | 0 | 0 |

N | 5 | Att | Gap | 40 | 0 | 0 |

N | 5 | Att | Gap | 40 | 0 | 0 |

N | 5 | Att | Gap | 40 | 0 | 0 |

N | 5 | Att | Gap | 40 | 0 | 0 |

N | 5 | Att | Gap | 40 | 0 | 0 |

N | 5 | Att | Gap | 40 | 0 | 0 |

O | 5 | Att | Gap | 25 | 0 | 0 |

O | 5 | Att | Gap | 25 | 0 | 0 |

O | 5 | Att | Gap | 25 | 0 | 0 |

O | 5 | Att | Gap | 25 | 0 | 0 |

O | 5 | Att | Gap | 25 | 0 | 0 |

P | 5 | Pen | Gap | 35 | 0 | 5 |

P | 5 | Att | Gap | 35 | 0 | 5 |

P | 5 | Att | Gap | 35 | 0 | 5 |

P | 5 | Att | Gap | 35 | 0 | 5 |

P | 5 | Att | Gap | 35 | 0 | 5 |

P | 5 | Att | Gap | 35 | 0 | 5 |

P | 5 | Att | Gap | 35 | 0 | 5 |

P | 5 | Att | Gap | 35 | 0 | 5 |

Q | 5 | Pen | Gap | 40 | 0 | 5 |

Q | 5 | Att | Gap | 40 | 0 | 5 |

Q | 5 | Att | Gap | 40 | 0 | 5 |

Q | 5 | Att | Gap | 40 | 0 | 5 |

Q | 5 | Att | Gap | 40 | 0 | 5 |

Q | 5 | Att | Gap | 40 | 0 | 5 |

Q | 5 | Att | Gap | 40 | 0 | 5 |

Q | 5 | Att | Gap | 40 | 0 | 5 |

Q | 5 | Att | Gap | 40 | 0 | 5 |

Thank you

Solved! Go to Solution.

1 ACCEPTED SOLUTION

Accepted Solutions

Highlighted

---------------------------------------

*Putting square pegs in round holes since 1972.*

##### I have a NEW book!

DAX Cookbook from Packt

Over 120 DAX Recipes!

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

Greg_Deckler

Super User IV

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-30-2020
05:40 AM

OK @moeconsult using the pattern supplied previously, those would look like the following. You would put ID in a visual and then you could have these measures:

```
Sum att no pp =
SUMX(
FILTER(
'Table',
[Status] = "Att" &&
[Type] <> "pp"
),
[Hours]
)
Sum att pp =
SUMX(
FILTER(
'Table',
[Status] = "Att" &&
[Type] = "pp"
),
[Hours]
)
Sum pen no pp =
SUMX(
FILTER(
'Table',
[Status] = "Pen" &&
[Type] <> "pp"
),
[Hours]
)
```

---------------------------------------

DAX Cookbook from Packt

Proud to be a Super User!

6 REPLIES 6

Highlighted
##

---------------------------------------

*Putting square pegs in round holes since 1972.*

##### I have a NEW book!

DAX Cookbook from Packt

Over 120 DAX Recipes!

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

Greg_Deckler

Super User IV

Re: SUMIFS WITH MULTIPLE CRITERIA

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-29-2020
10:06 PM

Not overly up on my Excel macro language syntax but in general you use something like this as a replacement for SUMIF(S):

SUMX(FILTER('Table',[Column] = "Something" && [Column1] = "Something else"),[Some Other Column])

You can also use || or OR instead of && for AND.

Tough to say exactly I see the sample source data but I don't see expected result or logic (not in Excel macro language) of how to get from source data to result. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

---------------------------------------

DAX Cookbook from Packt

Proud to be a Super User!

Highlighted
##

moeconsult

Helper V

Re: SUMIFS WITH MULTIPLE CRITERIA

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-30-2020
01:46 AM

Basically, what I am trying to archieve is :

**Sum of hours where status = "att" and type <> "on pp" by id**

**Sum of hours where status = "att" and type = "on pp" by id**

**Sum of hours where status = "pen" and type <> "on pp" by id**

Output column is color coded

ID | Hours | Status | Type | F2FA | On | F2FP |

A | 5 | Att | GaP | 40 | 0 | 0 |

A | 5 | Att | FACE | 40 | 0 | 0 |

A | 5 | Att | FACE | 40 | 0 | 0 |

A | 5 | Att | FACE | 40 | 0 | 0 |

A | 5 | Att | GaP | 40 | 0 | 0 |

A | 5 | Att | FACE | 40 | 0 | 0 |

A | 5 | Att | GaP | 40 | 0 | 0 |

A | 5 | Att | GaP | 40 | 0 | 0 |

B | 5 | Att | FACE | 120 | 0 | 0 |

B | 5 | Att | GaP | 120 | 0 | 0 |

B | 5 | Att | FACE | 120 | 0 | 0 |

B | 5 | Att | GaP | 120 | 0 | 0 |

B | 5 | Att | FACE | 120 | 0 | 0 |

B | 5 | Att | FACE | 120 | 0 | 0 |

B | 5 | Att | FACE | 120 | 0 | 0 |

B | 5 | Att | GaP | 120 | 0 | 0 |

B | 5 | Att | FACE | 120 | 0 | 0 |

B | 5 | Att | FACE | 120 | 0 | 0 |

B | 5 | Att | FACE | 120 | 0 | 0 |

B | 5 | Att | GaP | 120 | 0 | 0 |

B | 5 | Att | FACE | 120 | 0 | 0 |

B | 5 | Att | GaP | 120 | 0 | 0 |

B | 5 | Att | FACE | 120 | 0 | 0 |

B | 5 | Att | GaP | 120 | 0 | 0 |

B | 5 | Att | FACE | 120 | 0 | 0 |

B | 5 | Att | FACE | 120 | 0 | 0 |

B | 5 | Att | GaP | 120 | 0 | 0 |

B | 5 | Att | GaP | 120 | 0 | 0 |

B | 5 | Att | GaP | 120 | 0 | 0 |

B | 5 | Att | GaP | 120 | 0 | 0 |

B | 5 | Att | GaP | 120 | 0 | 0 |

B | 5 | Att | GaP | 120 | 0 | 0 |

C | 2.5 | Pen | Rem | 25 | 0 | 5 |

C | 2.5 | Pen | Rem | 25 | 0 | 5 |

C | 5 | Att | One | 25 | 0 | 5 |

C | 5 | Att | GaP | 25 | 0 | 5 |

C | 5 | Att | GaP | 25 | 0 | 5 |

C | 5 | Att | GaP | 25 | 0 | 5 |

C | 5 | Att | GaP | 25 | 0 | 5 |

D | 3 | Pen | Rem | 10 | 0 | 6 |

D | 3 | Pen | Rem | 10 | 0 | 6 |

D | 5 | Att | GaP | 10 | 0 | 6 |

D | 5 | Att | GaP | 10 | 0 | 6 |

E | 2.5 | Pen | Rem | 10 | 0 | 15.5 |

E | 2.5 | Pen | Rem | 10 | 0 | 15.5 |

E | 2.5 | Pen | Rem | 10 | 0 | 15.5 |

E | 3 | Pen | Rem | 10 | 0 | 15.5 |

E | 2.5 | Pen | Rem | 10 | 0 | 15.5 |

E | 2.5 | Pen | Rem | 10 | 0 | 15.5 |

E | 5 | Att | GaP | 10 | 0 | 15.5 |

E | 5 | Att | GaP | 10 | 0 | 15.5 |

F | 3 | Pen | Rem | 30 | 0 | 6 |

F | 3 | Pen | Rem | 30 | 0 | 6 |

F | 5 | Att | GaP | 30 | 0 | 6 |

F | 5 | Att | GaP | 30 | 0 | 6 |

F | 5 | Att | GaP | 30 | 0 | 6 |

F | 5 | Att | GaP | 30 | 0 | 6 |

F | 5 | Att | GaP | 30 | 0 | 6 |

F | 5 | Att | GaP | 30 | 0 | 6 |

G | 2.5 | Pen | Rem | 25 | 0 | 5 |

G | 2.5 | Pen | Rem | 25 | 0 | 5 |

G | 5 | Att | One | 25 | 0 | 5 |

G | 5 | Att | GaP | 25 | 0 | 5 |

G | 5 | Att | GaP | 25 | 0 | 5 |

G | 5 | Att | GaP | 25 | 0 | 5 |

G | 5 | Att | GaP | 25 | 0 | 5 |

H | 2 | Pen | Rem | 0 | 0 | 2 |

I | 3 | Pen | Rem | 80 | 0 | 6 |

I | 3 | Pen | Rem | 80 | 0 | 6 |

I | 5 | Att | GaP | 80 | 0 | 6 |

I | 5 | Att | GaP | 80 | 0 | 6 |

I | 5 | Att | GaP | 80 | 0 | 6 |

I | 5 | Att | GaP | 80 | 0 | 6 |

I | 5 | Att | GaP | 80 | 0 | 6 |

I | 5 | Att | GaP | 80 | 0 | 6 |

I | 5 | Att | GaP | 80 | 0 | 6 |

I | 5 | Att | GaP | 80 | 0 | 6 |

I | 5 | Att | GaP | 80 | 0 | 6 |

I | 5 | Att | GaP | 80 | 0 | 6 |

I | 5 | Att | GaP | 80 | 0 | 6 |

I | 5 | Att | GaP | 80 | 0 | 6 |

I | 5 | Att | GaP | 80 | 0 | 6 |

I | 5 | Att | GaP | 80 | 0 | 6 |

I | 5 | Att | GaP | 80 | 0 | 6 |

I | 5 | Att | GaP | 80 | 0 | 6 |

J | 2.5 | Pen | Rem | 0 | 0 | 10 |

J | 2.5 | Pen | Rem | 0 | 0 | 10 |

J | 2.5 | Pen | Rem | 0 | 0 | 10 |

J | 2.5 | Pen | Rem | 0 | 0 | 10 |

K | 2.5 | Pen | Rem | 0 | 0 | 10 |

K | 2.5 | Pen | Rem | 0 | 0 | 10 |

K | 2.5 | Pen | Rem | 0 | 0 | 10 |

K | 2.5 | Pen | Rem | 0 | 0 | 10 |

L | 3 | Pen | Rem | 0 | 0 | 6 |

L | 3 | Pen | Rem | 0 | 0 | 6 |

M | 5 | Pen | GaP | 0 | 0 | 10 |

M | 5 | Pen | GaP | 0 | 0 | 10 |

N | 5 | Att | GaP | 40 | 0 | 0 |

N | 5 | Att | GaP | 40 | 0 | 0 |

N | 5 | Att | GaP | 40 | 0 | 0 |

N | 5 | Att | GaP | 40 | 0 | 0 |

N | 5 | Att | GaP | 40 | 0 | 0 |

N | 5 | Att | GaP | 40 | 0 | 0 |

N | 5 | Att | GaP | 40 | 0 | 0 |

N | 5 | Att | GaP | 40 | 0 | 0 |

O | 5 | Att | GaP | 25 | 0 | 0 |

O | 5 | Att | GaP | 25 | 0 | 0 |

O | 5 | Att | GaP | 25 | 0 | 0 |

O | 5 | Att | GaP | 25 | 0 | 0 |

O | 5 | Att | GaP | 25 | 0 | 0 |

P | 5 | Pen | GaP | 35 | 0 | 5 |

P | 5 | Att | GaP | 35 | 0 | 5 |

P | 5 | Att | GaP | 35 | 0 | 5 |

P | 5 | Att | GaP | 35 | 0 | 5 |

P | 5 | Att | GaP | 35 | 0 | 5 |

P | 5 | Att | GaP | 35 | 0 | 5 |

P | 5 | Att | GaP | 35 | 0 | 5 |

P | 5 | Att | GaP | 35 | 0 | 5 |

Q | 5 | Pen | GaP | 40 | 0 | 5 |

Q | 5 | Att | GaP | 40 | 0 | 5 |

Q | 5 | Att | GaP | 40 | 0 | 5 |

Q | 5 | Att | GaP | 40 | 0 | 5 |

Q | 5 | Att | GaP | 40 | 0 | 5 |

Q | 5 | Att | GaP | 40 | 0 | 5 |

Q | 5 | Att | GaP | 40 | 0 | 5 |

Q | 5 | Att | GaP | 40 | 0 | 5 |

Q | 5 | Att | GaP | 40 | 0 | 5 |

I hope this helps so you can help me

Thanks.

Highlighted

---------------------------------------

*Putting square pegs in round holes since 1972.*

##### I have a NEW book!

DAX Cookbook from Packt

Over 120 DAX Recipes!

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

Greg_Deckler

Super User IV

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-30-2020
05:40 AM

OK @moeconsult using the pattern supplied previously, those would look like the following. You would put ID in a visual and then you could have these measures:

```
Sum att no pp =
SUMX(
FILTER(
'Table',
[Status] = "Att" &&
[Type] <> "pp"
),
[Hours]
)
Sum att pp =
SUMX(
FILTER(
'Table',
[Status] = "Att" &&
[Type] = "pp"
),
[Hours]
)
Sum pen no pp =
SUMX(
FILTER(
'Table',
[Status] = "Pen" &&
[Type] <> "pp"
),
[Hours]
)
```

---------------------------------------

DAX Cookbook from Packt

Proud to be a Super User!

Highlighted
##

Regards,

Ashish Mathur

http://www.ashishmathur.com

https://www.linkedin.com/in/excelenthusiasts/

Ashish_Mathur

Super User IV

Re: SUMS WITH MULTIPLE CRITERIA

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-30-2020
07:35 PM

Hi,

Has your question been solved?

Regards,

Ashish Mathur

http://www.ashishmathur.com

https://www.linkedin.com/in/excelenthusiasts/

Highlighted
##

moeconsult

Helper V

Re: SUMS WITH MULTIPLE CRITERIA

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-04-2020
05:29 AM

Yes thanks, I figured it out , sorry for the late reply

Highlighted
##

moeconsult

Helper V

Re: SUMIFS WITH MULTIPLE CRITERIA

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-04-2020
05:41 AM

@Greg_Deckler Thanks for your help, I have third condition for the Measure but I was able to get it done with Dax below:

Sum att no pp = **Calculate(****Sum('Table',[Hours]),****'Table',****[Status] = "Att",**

**'Table',****[Type] <> "pp",**

**'Table',**

**[Achieve]="Acce")**

Please let me know if you have any suggestion with the way have implemented this.

Regards

Announcements

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Visit our Community Blog for articles, guides, and information created by fellow community members.

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Top Kudoed Authors

User | Count |
---|---|

489 | |

215 | |

156 | |

124 | |

100 |