cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper V
Helper V

SUMS WITH MULTIPLE CRITERIA

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

IdHoursStateTypeF2FAIn F2FP
A5AttGap4000
A5AttFace4000
A5AttFace4000
A5AttFace4000
A5AttGap4000
A5AttFace4000
A5AttGap4000
A5AttGap4000
B5AttFace12000
B5AttGap12000
B5AttFace12000
B5AttGap12000
B5AttFace12000
B5AttFace12000
B5AttFace12000
B5AttGap12000
B5AttFace12000
B5AttFace12000
B5AttFace12000
B5AttGap12000
B5AttFace12000
B5AttGap12000
B5AttFace12000
B5AttGap12000
B5AttFace12000
B5AttFace12000
B5AttGap12000
B5AttGap12000
B5AttGap12000
B5AttGap12000
B5AttGap12000
B5AttGap12000
C2.5PenRem2505
C2.5PenRem2505
C5AttA2505
C5AttGap2505
C5AttGap2505
C5AttGap2505
C5AttGap2505
D3PenRem1006
D3PenRem1006
D5AttGap1006
D5AttGap1006
E2.5PenRem10015.5
E2.5PenRem10015.5
E2.5PenRem10015.5
E3PenRem10015.5
E2.5PenRem10015.5
E2.5PenRem10015.5
E5AttGap10015.5
E5AttGap10015.5
F3PenRem3006
F3PenRem3006
F5AttGap3006
F5AttGap3006
F5AttGap3006
F5AttGap3006
F5AttGap3006
F5AttGap3006
G2.5PenRem2505
G2.5PenRem2505
G5AttA2505
G5AttGap2505
G5AttGap2505
G5AttGap2505
G5AttGap2505
H2PenRem002
I3PenRem8006
I3PenRem8006
I5AttGap8006
I5AttGap8006
I5AttGap8006
I5AttGap8006
I5AttGap8006
I5AttGap8006
I5AttGap8006
I5AttGap8006
I5AttGap8006
I5AttGap8006
I5AttGap8006
I5AttGap8006
I5AttGap8006
I5AttGap8006
I5AttGap8006
I5AttGap8006
J2.5PenRem0010
J2.5PenRem0010
J2.5PenRem0010
J2.5PenRem0010
K2.5PenRem0010
K2.5PenRem0010
K2.5PenRem0010
K2.5PenRem0010
L3PenRem006
L3PenRem006
M5PenGap0010
M5PenGap0010
N5AttGap4000
N5AttGap4000
N5AttGap4000
N5AttGap4000
N5AttGap4000
N5AttGap4000
N5AttGap4000
N5AttGap4000
O5AttGap2500
O5AttGap2500
O5AttGap2500
O5AttGap2500
O5AttGap2500
P5PenGap3505
P5AttGap3505
P5AttGap3505
P5AttGap3505
P5AttGap3505
P5AttGap3505
P5AttGap3505
P5AttGap3505
Q5PenGap4005
Q5AttGap4005
Q5AttGap4005
Q5AttGap4005
Q5AttGap4005
Q5AttGap4005
Q5AttGap4005
Q5AttGap4005
Q5AttGap4005

@Ashish_Mathur @Greg_Deckler

Thank you

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV
Super User IV

Re: SUMIFS WITH MULTIPLE CRITERIA

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

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

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!

Proud to be a Super User!




View solution in original post

6 REPLIES 6
Highlighted
Super User IV
Super User IV

Re: SUMIFS WITH MULTIPLE CRITERIA

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


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

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!

Proud to be a Super User!




Highlighted
Helper V
Helper V

Re: SUMIFS WITH MULTIPLE CRITERIA

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

IDHoursStatusTypeF2FA On F2FP
A5AttGaP4000
A5AttFACE4000
A5AttFACE4000
A5AttFACE4000
A5AttGaP4000
A5AttFACE4000
A5AttGaP4000
A5AttGaP4000
B5AttFACE12000
B5AttGaP12000
B5AttFACE12000
B5AttGaP12000
B5AttFACE12000
B5AttFACE12000
B5AttFACE12000
B5AttGaP12000
B5AttFACE12000
B5AttFACE12000
B5AttFACE12000
B5AttGaP12000
B5AttFACE12000
B5AttGaP12000
B5AttFACE12000
B5AttGaP12000
B5AttFACE12000
B5AttFACE12000
B5AttGaP12000
B5AttGaP12000
B5AttGaP12000
B5AttGaP12000
B5AttGaP12000
B5AttGaP12000
C2.5PenRem2505
C2.5PenRem2505
C5AttOne2505
C5AttGaP2505
C5AttGaP2505
C5AttGaP2505
C5AttGaP2505
D3PenRem1006
D3PenRem1006
D5AttGaP1006
D5AttGaP1006
E2.5PenRem10015.5
E2.5PenRem10015.5
E2.5PenRem10015.5
E3PenRem10015.5
E2.5PenRem10015.5
E2.5PenRem10015.5
E5AttGaP10015.5
E5AttGaP10015.5
F3PenRem3006
F3PenRem3006
F5AttGaP3006
F5AttGaP3006
F5AttGaP3006
F5AttGaP3006
F5AttGaP3006
F5AttGaP3006
G2.5PenRem2505
G2.5PenRem2505
G5AttOne2505
G5AttGaP2505
G5AttGaP2505
G5AttGaP2505
G5AttGaP2505
H2PenRem002
I3PenRem8006
I3PenRem8006
I5AttGaP8006
I5AttGaP8006
I5AttGaP8006
I5AttGaP8006
I5AttGaP8006
I5AttGaP8006
I5AttGaP8006
I5AttGaP8006
I5AttGaP8006
I5AttGaP8006
I5AttGaP8006
I5AttGaP8006
I5AttGaP8006
I5AttGaP8006
I5AttGaP8006
I5AttGaP8006
J2.5PenRem0010
J2.5PenRem0010
J2.5PenRem0010
J2.5PenRem0010
K2.5PenRem0010
K2.5PenRem0010
K2.5PenRem0010
K2.5PenRem0010
L3PenRem006
L3PenRem006
M5PenGaP0010
M5PenGaP0010
N5AttGaP4000
N5AttGaP4000
N5AttGaP4000
N5AttGaP4000
N5AttGaP4000
N5AttGaP4000
N5AttGaP4000
N5AttGaP4000
O5AttGaP2500
O5AttGaP2500
O5AttGaP2500
O5AttGaP2500
O5AttGaP2500
P5PenGaP3505
P5AttGaP3505
P5AttGaP3505
P5AttGaP3505
P5AttGaP3505
P5AttGaP3505
P5AttGaP3505
P5AttGaP3505
Q5PenGaP4005
Q5AttGaP4005
Q5AttGaP4005
Q5AttGaP4005
Q5AttGaP4005
Q5AttGaP4005
Q5AttGaP4005
Q5AttGaP4005
Q5AttGaP4005

 

I hope this helps so you can help me 

@Greg_Deckler 

 

Thanks.

Highlighted
Super User IV
Super User IV

Re: SUMIFS WITH MULTIPLE CRITERIA

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

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

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!

Proud to be a Super User!




View solution in original post

Highlighted
Super User IV
Super User IV

Re: SUMS WITH MULTIPLE CRITERIA

Hi,

Has your question been solved?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Highlighted
Helper V
Helper V

Re: SUMS WITH MULTIPLE CRITERIA

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

Highlighted
Helper V
Helper V

Re: SUMIFS WITH MULTIPLE CRITERIA

@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

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

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

Community Blog

Community Blog

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

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors