PL Dashboard - Calculations in both Rows and Columns

selvaam12
selvaam12
in Modeling

Hi,

 

I need to create a PL report/dash board where there are calculations in both rows and columns as in the attached screen. How can I achieve this.

 

I have selections for Period, Year, Products and Region. Based on the period and Year selected, the data should show current YTD, prior YTD and % Change. These are part of columns. In Rows I need GL accounts Sales (account), COGS (account) and Profit (Calculated).

 

-- Selva.

Tagged:

Answers

  • M.Kierepka
    M.Kierepka

    Hi @selvaam12,

    Firstly build the module just with the base values, so base dimensions + Time + line items (Sales, COGS, Profit). Then create line item subset for this module and add these 3 line items to it. Next, you need to create CALCulation module, where you have all base dimensions + Line Item Subset + just one line item (can be called 'Values'). And the last step is to create your reporting module, just as you want, with all base dimensions + Line Item Subset + 3 line items (CYTD, PYTD, %Change), with respective formulas that will use your CALC module, like this:

    CYTD=TIMESUM(CALC.Values, SYS.CY Start Month, SYS.CY Current Month)
    PYTD=TIMESUM(CALC.Values, SYS.PY Start Month, SYS.PY Current Month)
    % Change = (CYTD - PYTD)/PYTD

Categories