Summing a Max or Level of Detail

AttuAk
AttuAk Member
edited March 2023 in Scheduled Reports

My data has row count information that is duplicated or reported on a Column Name basis. I need to be able to sum the max of each dataset name, or sum by level of detail - column name.

For example in the data shown below. The row count for 'KPI DOC DOE SUB BE - SBE as category' is 5,013,614,211 and the row count for 'MKTG - EVENTS DATA - EMAIL ECIDS - DEV' is 111. I want to report a Total Row Count in the Summary Number of 5,013,614,322, not the 145.4B that is currently being reported because it is summing up the entire of 'Row Count'.

Can this be done in a beast mode?

Capture.JPG


Answers

  • MarkSnodgrass
    MarkSnodgrass Coach

    A basic way to accomplish this in a beast mode is to use sum distinct like this:

    SUM(DISTINCT `Row Count`)

    However, this is not a perfect solution if you have names with the same row count. I'm not sure it is doable in a beast mode that is perfect, but you could do this in Magic ETL by doing a group by statement to get the summary number total that you are looking for and then join that back to your main dataset. This will create another column with that summary number showing for every row in your dataset, but then you can just reference that number in your summary number and just choose max or average as your aggregation type.

    Hope that makes sense.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • AttuAk
    AttuAk Member

    Thanks for the feedback. I think you are correct in that it will have to be done using ETL....I'll go that route. Thanks again.

Categories