Applying Formulas to Specific Rows in a Flat List While Keeping Line Items as Columns

SteveST

This question may have been asked and answered before, but I’d appreciate your thoughts on the following scenario.

If a Flat List is used as the rows in a module, is it possible to apply formulas to specific rows within that list?

In the example below, Revenue and COGS are pulled from a DAT module by using a SYS module that maps the Flat List items to their corresponding items in the DAT module.

However, I also need to calculate Total Revenue and Gross Margin as additional rows in the same view. The challenge is that the columns must be line items, rather than using line items as rows.

Is there a way to achieve this while keeping the existing Flat List structure, or would a different modeling approach be required?

Please see the image below and let me know your thoughts.

Thanks,
Steve

Flat list as row in the module	Column
Revenue 1	100.00
Revenue 2	100.00
Revenue 3	100.00
Revenue 4	100.00
Revenue 5	100.00
Revenue 6	100.00
Revenue	Sum(revenue 1: revenue 6)
COGS	200.00
Gross Margin	Revenue - COGS

jagadishdash

Hi @SteveST, you can achieve your requirement using a combination of a SYS mapping module and conditional formulas.

Use a sys module with conditioning logic,

you can retain your Flat List containing:

• Revenue 1 through Revenue 6
• Revenue (Total)
• COGS
• Gross Margin

In your SYS module, create the following line items:

Line Item	Format	Purpose
DAT Mapping	DAT List	Maps Revenue 1–6 and COGS to the source
Row Type	List	Identifies Detail, Total Revenue, or Gross Margin

Then, in your reporting module, which is dimensioned by the Flat List, use a formula similar to:

IF SYS.Row Type = Row Type.Total Revenue THEn Revenue Total ELSE IF SYS.Row Type = Row Type.Gross Margin THEN Revenue Total - COGS Total ELSE DAT.Value[LOOKUP: SYS.DAT Mapping]

Here, Revenue Total and COGS Total would be calculated in separate helper line items or modules.

You could create a Boolean line item in your SYS module called Is revenue?, marking Revenue 1 through Revenue 6 as TRUE.

Then calculate the total in a helper module using SUM with an appropriate mapping, or another aggregation approach.

For example, you could map the six revenue rows to a common Revenue category and aggregate them using SUM.

Hope this helps.