SUMIFS equivalent: Power BI / PowerPivot
Excel VBA Tools from Andrew
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: SUMIFS equivalent: Power BI / PowerPivot

  1. #1
    Board Regular
    Join Date
    Feb 2004
    Location
    Dublin
    Posts
    68
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default SUMIFS equivalent: Power BI / PowerPivot


    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. #2
    New Member
    Join Date
    Nov 2017
    Posts
    25
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #3
    MrExcel MVP
    Moderator
    Jerry Sullivan's Avatar
    Join Date
    Mar 2010
    Location
    San Diego, California
    Posts
    8,629
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    1 Thread(s)

    Default Re: SUMIFS equivalent: Power BI / PowerPivot

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

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

  4. #4
    Board Regular
    Join Date
    Feb 2004
    Location
    Dublin
    Posts
    68
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SUMIFS equivalent: Power BI / PowerPivot

    Quote Originally Posted by Jerry Sullivan View Post
    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. #5
    Board Regular
    Join Date
    Feb 2004
    Location
    Dublin
    Posts
    68
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com