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
    48
    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,754
    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

Some videos you may like

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
  •