Flexible SUM Aggregates With Duplicate Values in Dataset

Options
jmmc
jmmc Member

Hello everyone.

I have a data set that looks like this:


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
    Answer ✓
    Options

    @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.

    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
    Answer ✓
    Options

    @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.

    David Cunningham

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

  • ColemenWilson
    edited May 20
    Options

    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
    Options

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