SUMIFS equivalent: Power BI / PowerPivot

The Horse

Board Regular
Joined
Feb 20, 2004
Messages
68
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
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
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"
 
Upvote 0
You can add a measure to your PivotTable using this formula:

=CALCULATE(COUNTA(MyTable[Stock #]),MyTable[Indicator]="In")
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,492
Messages
6,113,967
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top