How do I group and sum certain columns ?

[EaglesTony]

Hi,

  I have the following sample data:

Key          Status 

123          Ready

123          Ready

123          In Progress

123          Done

123          Cancelled

What I need is 

Key     Not-Done-Items    Done-Cancelled-Items

123     3                            2

Is there a way to group it this way and get a sum total ?

[Sandhya1234]

Try it in the Matrix Table.

[EaglesTony]

I was able to use a measure for this.

ItemsOpen = COUNTROWS(FILTER(Table1, Table1[STATUS] <> "Done" && Table1[STATUS] <> "Cancelled" ))

ItemsDone = COUNTROWS(FILTER(Table1, Table1[STATUS] = "Done" || Table1[STATUS] = "Cancelled" ))

This works, however when I try to replace nulls with 0, I get an circular reference.

First I have:

ItemsOpenChangeNulls = IF (ISBLANK([ItemsOpen]), 0, [ItemsOpen]) ==> This works

When I try to add the following, I get a circular reference error, not sure how to get around it ??????

ItemsNotOpenChangeNulls = IF (ISBLANK([ItemsDone]), 0, [ItemsDone])

[Sandhya1234]

@EaglesTony Please try the below measures:

1.Done-Items =

COUNTROWS(

    FILTER(

        'Table (4)',

        'Table (4)'[Ready] = "Done"

    )

)

2.Not-Done-Items =

COUNTROWS(

    FILTER(

        'Table (4)',

        'Table (4)'[Ready] = "Not Done"

    )

)

[EaglesTony]

I think you mean [Status] = "Done" ?

Is there a way to do this in PowerQuery instead of DAX ?

[Anonymous · Accepted solution]

Hi @EaglesTony ,

Please refer to the steps below:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRCkpNTKlUitXBxffMUwgoyk8vSi0uRhJ1yc9LReI6J+Ylp+bkpKaAxYyMTVAMgvDRDYKIwg2CcJEMigUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Key = _t, Status = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Key", Int64.Type}, {"Status", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Key"}, {{"Not-Done-Items", each Table.RowCount(Table.SelectRows(_, each [Status] = "Ready" or [Status] = "In Progress")), Int64.Type}, {"Done-Cancelled-Items", each Table.RowCount(Table.SelectRows(_, each [Status] = "Done" or [Status] = "Cancelled")), Int64.Type}})
in
    #"Grouped Rows"

Output:

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum