Fixed Function At Two Levels (Or nested Fixed Function)

jmmc

I have a data set that includes duplicate rows for products.

I'm using a fixed function to de-dupe an aggregate sum of product prices because I only want one product row to contribute to the total.

The fixed function (total_product_price) looks like this:
SUM(DISTINCT Price) FIXED (BY Product ID)


However, now I need to create a table that looks like this:

Store	Total Product Price	Total Price	Percent of Total
A	100	300	0.33
B	100	300	0.33
C	100	300	0.33

The challenge is that the Total Price calculation must be a sum of the total_product_price calculation that is fixed to the entire data set (including the de-duplication in total_product_price performed by the Fixed Function).

Thank you in advance!

ColemenWilson

Have you tried using nested beastmodes? You may need to have nested beastmodes enabled by your CSM from Domo. You could try SUM(DISTINCT Price) FIXED (BY Product ID) as Beastmode 1 and then:

SUM(SUM(`Beastmode 1`) FIXED ())

ColemenWilson

Is ETL out of the question?

jmmc

@ColemenWilson , it is the plan right now, to group the values and de-dupe there, but that will lock us into a those set aggregation fields and prevent responsive filters.

After hacking, I wasn't able to get the fixed function to work as we'd like.

ColemenWilson

Have you tried using nested beastmodes? You may need to have nested beastmodes enabled by your CSM from Domo. You could try SUM(DISTINCT Price) FIXED (BY Product ID) as Beastmode 1 and then:

SUM(SUM(`Beastmode 1`) FIXED ())

jmmc

@ColemenWilson - I will try this. It's actually what I thought of as I typed out the OP.


Thanks, I'll follow up if it works!