Flexible SUM Aggregates With Duplicate Values in Dataset

jmmc

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.

david_cunningham

@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

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

ColemenWilson

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?

jmmc

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