Forum Discussion

EaglesTony's avatar
EaglesTony
Post Prodigy
2 years ago
Solved

How do I group and sum certain columns ?

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 ?

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      EaglesTony
      Post Prodigy
      2 years ago

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

       

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Try it in the Matrix Table.

     

    • EaglesTony's avatar
      EaglesTony
      Post Prodigy
      2 years ago

      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])