Flexible SUM Aggregates With Duplicate Values in Dataset

jmmc
jmmc Member
in Magic ETL

Hello everyone.

I have a data set that looks like this:

image.png


This data set shows product purchases at the customer level. Thus, product IDs are duplicated in the data set.

Here's the challenge.

I want to show the total value of products, counting each product value only once in a SUM. I've added a Product Rank column as a helper, so that the SUM formula can look like this:
SUM(CASE WHEN Product Rank = 1 then Price END)

The issue is when I add a filter to the dashboard for Customer Type (say, for "In Store"), then that SUM formula will not capture the total value because Product Rank = 2.

It seems what I'd need to do is run my rank and window on the fly based on the filters that are selected. Essentially, the issue is needing a way to de-duplicate the values that contribute towards the SUM calculation.

Tagged:

Best Answer

  • david_cunningham
    david_cunningham Coach
    Answer ✓

    @jmmc you can do this with SUM(DISTINCT ())

    SUM(DISTINCT Price) FIXED (BY Product ID)

    Yields the following results. You can see that the column fixed1 only includes the total once per id.

    image.png
    Screen Shot 2024-05-20 at 3.08.16 PM.png

    David Cunningham

    ** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
    ** Did this solve your problem? Accept it as a solution! ✔️**

Answers

  • david_cunningham
    david_cunningham Coach
    Answer ✓

    @jmmc you can do this with SUM(DISTINCT ())

    SUM(DISTINCT Price) FIXED (BY Product ID)

    Yields the following results. You can see that the column fixed1 only includes the total once per id.

    image.png
    Screen Shot 2024-05-20 at 3.08.16 PM.png

    David Cunningham

    ** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
    ** Did this solve your problem? Accept it as a solution! ✔️**

  • ColemenWilson
    ColemenWilson Coach
    edited May 20

    I am confused on how these are duplicates. Are they not separate transactions? For example, one online and one in store in the sample data above? If you want to see total value, why would you want to exclude customers?

    If I solved your problem, please select "yes" above

  • jmmc
    jmmc Member

    @david_cunningham this is excatly what I was looking for, this is a very powerful feature. Thank you!

Categories