Helper II

1 year ago

Solved

P&L Statement - Show elements with no data - Matrix

Hi,

we have a problem within our financial Report and the integated costcenter.

As you can see we have one Measure which creates the P&L lines. It has to be created row by row because the sum of the 2nd level is not the result of the first level line. (See Gross Margin as an Example).

If i enable the function "Show elements with no data" within the Matrix Row. I receive the entire P&L correctly, even if there are no data within the line.

The disadvantage of enabling "elements with no data - enabled" is that i now have a lot of costcenter within the P&L lines, with no values.

If i disable the function "Show elements with no data" i do only receive the costcenter, where i have some values posted. (Thats what i want)

But now i have the disadvantage, that now some summed postions like the Operating EBIT are missing (Level 1 Row).

What i want to achieve is:

The rows "CM_POSTION_LVL_1" + "CM_POSITION_LVL_2" should be shown at any time (Show Elements with no data - enabled). The 3rd row "Costcenter" should only be shown if there are some values posted on it "Show Elements with no data - disabled).

Till now i did not find a solution to creat it, even i am not sure if it is possible.

I´d be very grateful for some help.

Sample File:

https://www.dropbox.com/scl/fi/uh9e4tb3bm7jhnh00sfe1/Element-with-no-Data-Sample.pbix?rlkey=qmsg7n56fu5gbu5grhv5ix19o&st=7tj2ipn2&dl=0

Need Help

MFelix

1 year ago

Hi Quakker ,

Try the following code:

AYEAR = 
    VAR AYEAR_VARIABLE = IF(
        ISINSCOPE(LKP_CM_SCHEME[CM_POSITION_LVL_2]),
        SWITCH(
            SELECTEDVALUE(LKP_CM_SCHEME[CM_POSITION_LVL_2]),
                "Direct Revenues", CALCULATE(SUM(DATA_FALSE[AYEAR]), DATA_FALSE[POS_NR_LVL2] = 10),
                "Indirect Revenues", CALCULATE(SUM(DATA_FALSE[AYEAR]), DATA_FALSE[POS_NR_LVL2] = 20),
                "Other Revenues", CALCULATE(SUM(DATA_FALSE[AYEAR]), DATA_FALSE[POS_NR_LVL2] = 30),
                "Direct Costs", CALCULATE(SUM(DATA_FALSE[AYEAR]), DATA_FALSE[POS_NR_LVL2] = 40),
                "Indirect Costs", CALCULATE(SUM(DATA_FALSE[AYEAR]), DATA_FALSE[POS_NR_LVL2] = 50),
                "Other Costs", CALCULATE(SUM(DATA_FALSE[AYEAR]), DATA_FALSE[POS_NR_LVL2] = 60),
                "Thereof Transport Margin", CALCULATE(SUM(DATA_FALSE[AYEAR]), DATA_FALSE[POS_NR_LVL2] = 70),
                "Production Costs GER", CALCULATE(SUM(DATA_FALSE[AYEAR]), DATA_FALSE[POS_NR_LVL2] = 80),
                "Production Costs FRA", CALCULATE(SUM(DATA_FALSE[AYEAR]), DATA_FALSE[POS_NR_LVL2] = 90),
                "Production Costs USA", CALCULATE(SUM(DATA_FALSE[AYEAR]), DATA_FALSE[POS_NR_LVL2] = 100),
                "Production Costs Other", CALCULATE(SUM(DATA_FALSE[AYEAR]), DATA_FALSE[POS_NR_LVL2] = 110),
                "Service", CALCULATE(SUM(DATA_FALSE[AYEAR]), DATA_FALSE[POS_NR_LVL2] = 120),
                "Management", CALCULATE(SUM(DATA_FALSE[AYEAR]), DATA_FALSE[POS_NR_LVL2] = 130),
                "IT", CALCULATE(SUM(DATA_FALSE[AYEAR]), DATA_FALSE[POS_NR_LVL2] = 140),
                "Project Costs", CALCULATE(SUM(DATA_FALSE[AYEAR]), DATA_FALSE[POS_NR_LVL2] = 150),
                "Support Functions", CALCULATE(SUM(DATA_FALSE[AYEAR]), DATA_FALSE[POS_NR_LVL2] = 160),
                "Extraordinary Costs", CALCULATE(SUM(DATA_FALSE[AYEAR]), DATA_FALSE[POS_NR_LVL2] = 170),
            BLANK()  -- return BLANK() if no match is found
        ),
        SWITCH(
            SELECTEDVALUE(LKP_CM_SCHEME[CM_POSITION_LVL_1]),
                "Revenues", CALCULATE(SUM(DATA_FALSE[AYEAR]), DATA_FALSE[position_nr] = 10),
                "Direct Costs", CALCULATE(SUM(DATA_FALSE[AYEAR]), DATA_FALSE[position_nr] = 20),
                "Gross Margin", CALCULATE(SUM(DATA_FALSE[AYEAR]), DATA_FALSE[position_nr] = 10)+CALCULATE(SUM(DATA_FALSE[AYEAR]), DATA_FALSE[position_nr] = 20),
                "Contribution Margin", CALCULATE(SUM(DATA_FALSE[AYEAR]), DATA_FALSE[position_nr] = 10)+CALCULATE(SUM(DATA_FALSE[AYEAR]), DATA_FALSE[position_nr] = 20) + CALCULATE(SUM(DATA_FALSE[AYEAR]), DATA_FALSE[POS_NR_LVL2] IN {80, 90, 100, 110}),
                "EBIT", CALCULATE(SUM(DATA_FALSE[AYEAR]), DATA_FALSE[position_nr] = 70), -- irrelevant for sample
                "Operating EBIT", CALCULATE(SUM(DATA_FALSE[AYEAR]), DATA_FALSE[position_nr] = 80), -- irrelevant for sample
                BLANK()  -- return BLANK() if no match is found
        )
    )
    RETURN
     IF(
        ISINSCOPE(DATA_FALSE[POS_NR_LVL2]) || ISINSCOPE(DATA_FALSE[Costcenter]),
        AYEAR_VARIABLE,
        COALESCE(AYEAR_VARIABLE, "<u+200f><u+200f><u+200e> <u+200e>")
)

ch is found ),

nbsp; found ),

ewsem4i603E43FDD7532C48ng

nbsp;4i603E43FDD7532C48ng

Please se file attached.ng SWITCH(

lease se file attached.ng

nbsp; se file attached.ng

Element with no Data - Sample.pbix64 KB

3 Replies

MFelix

Super User

1 year ago

Hi Quakker ,

Try the following code:

AYEAR = 
    VAR AYEAR_VARIABLE = IF(
        ISINSCOPE(LKP_CM_SCHEME[CM_POSITION_LVL_2]),
        SWITCH(
            SELECTEDVALUE(LKP_CM_SCHEME[CM_POSITION_LVL_2]),
                "Direct Revenues", CALCULATE(SUM(DATA_FALSE[AYEAR]), DATA_FALSE[POS_NR_LVL2] = 10),
                "Indirect Revenues", CALCULATE(SUM(DATA_FALSE[AYEAR]), DATA_FALSE[POS_NR_LVL2] = 20),
                "Other Revenues", CALCULATE(SUM(DATA_FALSE[AYEAR]), DATA_FALSE[POS_NR_LVL2] = 30),
                "Direct Costs", CALCULATE(SUM(DATA_FALSE[AYEAR]), DATA_FALSE[POS_NR_LVL2] = 40),
                "Indirect Costs", CALCULATE(SUM(DATA_FALSE[AYEAR]), DATA_FALSE[POS_NR_LVL2] = 50),
                "Other Costs", CALCULATE(SUM(DATA_FALSE[AYEAR]), DATA_FALSE[POS_NR_LVL2] = 60),
                "Thereof Transport Margin", CALCULATE(SUM(DATA_FALSE[AYEAR]), DATA_FALSE[POS_NR_LVL2] = 70),
                "Production Costs GER", CALCULATE(SUM(DATA_FALSE[AYEAR]), DATA_FALSE[POS_NR_LVL2] = 80),
                "Production Costs FRA", CALCULATE(SUM(DATA_FALSE[AYEAR]), DATA_FALSE[POS_NR_LVL2] = 90),
                "Production Costs USA", CALCULATE(SUM(DATA_FALSE[AYEAR]), DATA_FALSE[POS_NR_LVL2] = 100),
                "Production Costs Other", CALCULATE(SUM(DATA_FALSE[AYEAR]), DATA_FALSE[POS_NR_LVL2] = 110),
                "Service", CALCULATE(SUM(DATA_FALSE[AYEAR]), DATA_FALSE[POS_NR_LVL2] = 120),
                "Management", CALCULATE(SUM(DATA_FALSE[AYEAR]), DATA_FALSE[POS_NR_LVL2] = 130),
                "IT", CALCULATE(SUM(DATA_FALSE[AYEAR]), DATA_FALSE[POS_NR_LVL2] = 140),
                "Project Costs", CALCULATE(SUM(DATA_FALSE[AYEAR]), DATA_FALSE[POS_NR_LVL2] = 150),
                "Support Functions", CALCULATE(SUM(DATA_FALSE[AYEAR]), DATA_FALSE[POS_NR_LVL2] = 160),
                "Extraordinary Costs", CALCULATE(SUM(DATA_FALSE[AYEAR]), DATA_FALSE[POS_NR_LVL2] = 170),
            BLANK()  -- return BLANK() if no match is found
        ),
        SWITCH(
            SELECTEDVALUE(LKP_CM_SCHEME[CM_POSITION_LVL_1]),
                "Revenues", CALCULATE(SUM(DATA_FALSE[AYEAR]), DATA_FALSE[position_nr] = 10),
                "Direct Costs", CALCULATE(SUM(DATA_FALSE[AYEAR]), DATA_FALSE[position_nr] = 20),
                "Gross Margin", CALCULATE(SUM(DATA_FALSE[AYEAR]), DATA_FALSE[position_nr] = 10)+CALCULATE(SUM(DATA_FALSE[AYEAR]), DATA_FALSE[position_nr] = 20),
                "Contribution Margin", CALCULATE(SUM(DATA_FALSE[AYEAR]), DATA_FALSE[position_nr] = 10)+CALCULATE(SUM(DATA_FALSE[AYEAR]), DATA_FALSE[position_nr] = 20) + CALCULATE(SUM(DATA_FALSE[AYEAR]), DATA_FALSE[POS_NR_LVL2] IN {80, 90, 100, 110}),
                "EBIT", CALCULATE(SUM(DATA_FALSE[AYEAR]), DATA_FALSE[position_nr] = 70), -- irrelevant for sample
                "Operating EBIT", CALCULATE(SUM(DATA_FALSE[AYEAR]), DATA_FALSE[position_nr] = 80), -- irrelevant for sample
                BLANK()  -- return BLANK() if no match is found
        )
    )
    RETURN
     IF(
        ISINSCOPE(DATA_FALSE[POS_NR_LVL2]) || ISINSCOPE(DATA_FALSE[Costcenter]),
        AYEAR_VARIABLE,
        COALESCE(AYEAR_VARIABLE, "<u+200f><u+200f><u+200e> <u+200e>")
)

ch is found ),

nbsp; found ),

ewsem4i603E43FDD7532C48ng

nbsp;4i603E43FDD7532C48ng

Please se file attached.ng SWITCH(

lease se file attached.ng

nbsp; se file attached.ng

Element with no Data - Sample.pbix64 KB

Quakker
Helper II
1 year ago
Hi MFelix,
can´t believe that is was just a simple adjustment within the code.
Thank you very much for your help!
I really thought it´s not possible

Daniel29195
Community Champion
1 year ago
hello Quakker

try this :

disable the show items with no data.

for the rows that you want to return even if there is no data, try returning empty character instead.

let me know if this works

Forum Discussion

P&L Statement - Show elements with no data - Matrix

3 Replies

Related content

P&L - Paths

rsQueryMemoryLimitExceeded Error in P&L / Balance Sheet Measure (Small Dataset)

P&L variances (formatting % and absolute values)

Finance P&L: Emerald Performance Theme

Build Balance and P&L