How to group rows and make columns that count specific values?

JoeR_PSIT
JoeR_PSIT Member
edited March 2023 in Datasets

Hi!

This is a difficult one to give a title but basically, here is what I am trying to do:

I have a dataset that combines a dataset of user profiles and a dataset of our assets that are assigned to each user. What this does is make it so every single entry for a user's individual assets gets its own row. So basically, if John Smith has a Laptop and a Mobile Phone, he'll have two complete rows like so:

User: John Smith | AssetType: Laptop | DeviceName: John's Mac | etc

User: John Smith | AssetType: Mobile Phone | DeviceName: John's iPhone | etc

What I really want this dataset to be is a high level breakdown of each user, how many assets they have, and how many of each type of asset they have. So theoretically, I'd like it to be something like this:

User: John Smith | Number of Assets: 2 | Number of Laptops: 1 | Number of Mobile Phones: 1

I can see where I can group up the rows by username and get a count of the AssetType, but I'd like to have columns in the dataset that break things down like the above example if possible. What sort of steps would I need to take to do something like that?

Any help is appreciated!

Tagged:

Best Answer

  • Paul_M_DOMO
    Paul_M_DOMO Domo Employee
    Answer ✓

    Hey JoeR


    At a glance I might add 2 filters. 1 that gets asset = phone one that gets asset = laptop then do a group by user to create a new column that counts how many times that user appears in the dataset. This count will be your "total laptop" and "total phone". You could then join back on the user to have 1 row containing both and do a calculation to sum the 2 at the end to get total assets.

    image.png


    Hope this helps!

    PT

Answers

  • Paul_M_DOMO
    Paul_M_DOMO Domo Employee
    Answer ✓

    Hey JoeR


    At a glance I might add 2 filters. 1 that gets asset = phone one that gets asset = laptop then do a group by user to create a new column that counts how many times that user appears in the dataset. This count will be your "total laptop" and "total phone". You could then join back on the user to have 1 row containing both and do a calculation to sum the 2 at the end to get total assets.

    image.png


    Hope this helps!

    PT

Categories