SUMIFS equivalent: Power BI / PowerPivot

[The Horse]

Help !!

Been struggling to find the correct DAX formula for a column - the equivalent of a SUMIFS in Excel, to obtain a unique count for "In" items by stock number (below).

For Excel, =COUNTIFS($A$2:$A$11,$A2,$B$2:$B$11,"=In") does the job

Stock # Indicator Count
24461875 In 2
24461875 In 2
24497193 In 2
24497193 In 2
24497296 In 3
24497296 In 3
24497296 Out 3
24497296 In 3
24497301 In 1
24497301 Out 1

What's the DAX (am totally frustrated that I can't do this !!)
Many thanks

The Horse

 

[horseyride]

In M, if it helps, it would be

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Stock #", Int64.Type}, {"Indicator", type text}, {"Count", Int64.Type}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Indicator] = "In")),
#"Grouped Rows" = Table.Group(#"Filtered Rows", {"Stock #"}, {{"Count", each List.Sum([Count]), type number}})
in
#"Grouped Rows"

 

[Jerry Sullivan]

You can add a measure to your PivotTable using this formula:

=CALCULATE(COUNTA(MyTable[Stock #]),MyTable[Indicator]="In")

 

[The Horse]

You can add a measure to your PivotTable using this formula:

=CALCULATE(COUNTA(MyTable[Stock #]),MyTable[Indicator]="In")

Jerry,

Thank you - worked perfectly !
Thanks @horseyride...when I get my head around M I'll try it out.

Regards to all in the Mr Excel community

The Horse

 

[The Horse]

Update !

By burrowing in Rob Collie's brilliant blog, I found the best way to produce a Sumif count in the PowerBI new column (Count) from the data:

Stock # Indicator Value Count
24461875 In 10 2
24461875 In 20 2
24497193 In 30 2
24497193 In 40 2
24497296 In 50 3
24497296 In 60 3
24497296 Out 70 3
24497296 In 80 3
24497301 In 90 1
24497301 Out 100 1

Being:

Measure=CALCULATE(COUNT([Value]),FILTER(ALL(‘TableName’),’TableName’[Stock #]=EARLIER(‘TableName’[Stock #])),FILTER(ALL(‘TableName’),’TableName’[Indicator]=”In”))

Hope this is of help to someone out there in the future !

Regards

The Horse