Thanks:  0
Likes:  0

# Thread: SUMIFS equivalent: Power BI / PowerPivot

1. ## SUMIFS equivalent: Power BI / PowerPivot

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

2. ## Re: SUMIFS equivalent: Power BI / PowerPivot

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"

3. ## Re: SUMIFS equivalent: Power BI / PowerPivot

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

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

4. ## Re: SUMIFS equivalent: Power BI / PowerPivot

Originally Posted by Jerry Sullivan
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

5. ## Re: SUMIFS equivalent: Power BI / PowerPivot

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

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•