How can I get additional Groups in this Pivot Table?

Options
Barb
Barb Member

I'm trying to produce a table similar to this one in DOMO so I can get it out of Excel:

I am currently using a pivot table and have everything working EXCEPT the 'Forecast', 'Actual' and 'Actual v. Forecast' groupings. The data is a combination of actual fields and beast mode calculations.

Any ideas on how I can do this in a similar fashion? I thought about going back to the ETL, but with some being Beast Mode calculations, I don't think that will work. Do I need to use something other than a pivot table?

Best Answer

  • Jonathan53891
    Jonathan53891 Contributor
    edited June 24 Answer ✓
    Options

    Thank you for the additional context, @Barb. Here's what I recommend doing for the time being:

    • Move your 'Actual v. Forecast' and ROAS calculations into the ETL
    • Have two separate output datasets in the ETL: One that contains the current output dataset you are using to power all of those cards without any changes made to it, and a separate output dataset that includes the calculations
    • Replace the dataset in the pivot table card with the new output dataset you just created

    By doing this, you can build your ETL in such a way where you are able to incorporate the grouping logic in the dataflow without disrupting your existing visualizations.

Answers

  • Jonathan53891
    Jonathan53891 Contributor
    Options

    Hi @Barb,

    Would you be able to clarify which of the columns in your pivot table are using Beast Mode calculations? If you are just using Beast Mode calculations for the variance columns in the "Actual vs Forecast" grouping, you can certainly move those into the ETL and create the grouping logic in the sample screenshot you provided.

  • Barb
    Barb Member
    Options

    I'm using it on 'Actual v. Forecast' and the ROAS calculations. My concern about moving the 'Actual v. Forecast' metric to the ETL is that the output on this is significant and is used on many visualizations (e.g. by month, marketing tactic, period-over-period reporting on a fiscal calendar, sub-level marketing tactics) but I'm open to suggestions.

  • Jonathan53891
    Jonathan53891 Contributor
    edited June 24 Answer ✓
    Options

    Thank you for the additional context, @Barb. Here's what I recommend doing for the time being:

    • Move your 'Actual v. Forecast' and ROAS calculations into the ETL
    • Have two separate output datasets in the ETL: One that contains the current output dataset you are using to power all of those cards without any changes made to it, and a separate output dataset that includes the calculations
    • Replace the dataset in the pivot table card with the new output dataset you just created

    By doing this, you can build your ETL in such a way where you are able to incorporate the grouping logic in the dataflow without disrupting your existing visualizations.

  • Barb
    Barb Member
    Options

    Thanks @Jonathan53891 !

  • Jonathan53891
    Jonathan53891 Contributor
    Options

    No problem, @Barb! Let me know if you are able to complete that and I can assist you with the grouping logic from there.

  • haipham
    haipham Member
    Options

    Hello @Jonathan53891, Would you please share what grouping logic you would do in this scenario? Thank you!