# SUMIFS equivalent: Power BI / PowerPivot



## The Horse (Nov 21, 2017)

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 (Nov 21, 2017)

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 (Nov 21, 2017)

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

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


----------



## The Horse (Nov 22, 2017)

Jerry Sullivan said:


> 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 (Dec 5, 2017)

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


----------

