How can I get additional Groups in this Pivot Table?
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
-
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.
1
Answers
-
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.
0 -
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.
0 -
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.
1 -
Thanks @Jonathan53891 !
0 -
No problem, @Barb! Let me know if you are able to complete that and I can assist you with the grouping logic from there.
1 -
Hello @Jonathan53891, Would you please share what grouping logic you would do in this scenario? Thank you!
0 -
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.
1 -
Awesome! I appreciate your detailed explanation, @Jonathan53891! Thank you!!!
1
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 744 Beast Mode
- 58 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 395 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 6 Software Integrations
- 125 Manage
- 122 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive