Fixed Function At Two Levels (Or nested Fixed Function)

Options
jmmc
jmmc Member

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!


Tagged:

Best Answer

  • ColemenWilson
    Answer ✓
    Options

    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 ())
    

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

Answers

  • ColemenWilson
    edited June 18
    Options

    Is ETL out of the question?

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

  • jmmc
    jmmc Member
    Options

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

    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 ())
    

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

  • jmmc
    jmmc Member
    Options

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