Help with which Action Tiles To Use

PJG

Hi all!

I'm back with more beginner level questions probably :)

I feel like I've done the complex piece for this which is writing 24 different Beast Modes. I'm just struggling with which Actions to use in the ETL to bring all these Beast Modes together at once.

This looks like a fairly useless mockup because there's no values in it, and it's heavily simplified, but let me explain. The input table has one row per project. In the output, I need to SUM these based on my Beast Modes for all projects, but each row in the output - in fact, each cell - needs a different Beast Mode using other columns from the Input in their calculation (not shown).

Technically, the output is what I need my Card to look like, but I think the Dataset can look the same.

Just as an example of what I'm talking about, for the first row in the Output, Baseline, these are my three different Beast Modes

2024 CAPEX

SUM(CASE WHEN RecordType = 'Live' AND YEAR(Month)=2024 AND FinancialCategoryName LIKE '%CAPEX%' THEN PlannedCost

ELSE 0

END)

2024 OPEX

SUM(CASE WHEN RecordType = 'Live' AND YEAR(Month)=2024 AND FinancialCategoryName LIKE '%OPEX%' THEN PlannedCost

ELSE 0

END)

2024 Run Cost

MAX(CASE

WHEN RecordType = 'Live' AND First Year of Impact_p ='2024' THEN Yearly Total RUN Costs (CHF)_p

WHEN RecordType = 'Live' AND First Year of Impact_p IS NULL THEN Yearly Total RUN Costs (CHF)_p

ELSE 0

END)

So that's just one row of the output, and then each subsequent row has a different formula per cell.

How do I bring it all together into a single table? Just struggling with which Actions to use and in what order. Thanks!

rco

From your screenshots I'm assuming you have three formulas for each of five rows, that's where the numbers below are coming from.

Start with an Input DataSet tile. We'll split that tile's output out to five Add Constants tiles, where we'll add a column called "Row Name" to each, with values "Baseline", "Rejected", "On-Hold", etc.

After each Add Constants comes a Group By, which uses "Row Name" as its grouping column. Each Group By gets three formula aggregates, which are the three formulas that correspond to the relevant Row Name for that path.

Finally, we'll use an Append Rows tile to combine the rows together into one table. Since all the paths have the same column names, it shouldn't matter how you configure that tile.