How can I get additional Groups in this Pivot Table?

Barb
Barb Member
edited June 24 in Cards, Dashboards, Stories

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

image.png

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 ✓

    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

    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

    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 ✓

    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

    Thanks @Jonathan53891 !

  • Jonathan53891
    Jonathan53891 Contributor

    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

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

  • Jonathan53891
    Jonathan53891 Contributor

    Hi @haipham,

    I'm not sure how closely this translates to this particular use case, but I can generally explain how to handle the grouping logic in a similar scenario.

    My general recommendation would be to split the data between forecast and actual data so they are calculated separately. Then I would use an Add Constants ETL tile where the new column being created is named "DataSet" and the values would be either Forecast, Actual, or Variance depending on which branch of the dataflow is performing what calculation. Then, I would ensure the Spend, Revenue, and ROAS columns all have the same column header naming. Then, I would append the datasets to each other and have that be my final output dataset. For the sake of argument, the columns in your output dataset could look like the following: Marketing Type, Date, DataSet, Spend, Revenue, ROAS.

    Then at the card level, you would simply need to place the Marketing Type column in the "Rows" section, the Date and DataSet columns in the "Columns" section, and the Spend and Revenue and ROAS columns in the "Values" section. As an additional note, I would make sure the Date column is placed above the DataSet column after saving & closing the card. If you do it the other way around like screenshot suggests in the example above, you lose the native "Graph By" functionality of the pivot table card, and you would need to hard code MONTHNAME(`Date`) in the dataflow or a Beast Mode calculation, which can't be used dynamically.

  • haipham
    haipham Member

    Awesome! I appreciate your detailed explanation, @Jonathan53891! Thank you!!!

Categories