Forum Discussion

Alta88's avatar
Alta88
Helper IV
4 years ago
Solved

How to count different items in the same column

I've got a report that has 3 different appointment types listed under the same column heading. 2 types are lumped together and 1 is it's own category. I need to get the total of each appointment type...
Need Help
  • jsaunders_zero9's avatar
    jsaunders_zero9
    4 years ago

    Alta88 

    See below steps to produce using the UI.

     

    1. Add Custom Column

    2. Create if statement to determine which "Group" the items should fall into.

    if [Appointment Type] = "Service" then "Service" else "LVLn"

    3. Add Group By, right click the column you just created call TypeGroup, Select Group By

    4. Group By Appointment Date & TypeGroup

    5. End Result

Alta88's avatar
Alta88
Helper IV
4 years ago

for example: 

 

 

Levels 1 and 2 are the ones lumped together. 

  • jsaunders_zero9's avatar
    jsaunders_zero9
    Responsive Resident
    4 years ago

    Could you add a helper column to group on?

    = Table.AddColumn(#"Changed Type", "TypeGroup", each if [Appointment Type] = "Service" then "Service" else "LVLn")

    Then group the new column on Appointment Date and the TypeGroup column

    = Table.Group(#"Added Custom", {"Appointment Date", "TypeGroup"}, {{"Count", each Table.RowCount(_), type number}})