Help with code optimization

DAX DAX Calculations
1

I am asking for help in optimizing the following metric.

% of Sales revenue =
IF (
    SELECTEDVALUE ( PnL[Position Level2] ) = "Online"
        && SELECTEDVALUE ( PnL[Position Level1] ) = "COGS",
    DIVIDE (
        [P&L AC],
        CALCULATE (
            [P&L AC],
            ALL (
                PnL[Sortowanie level 1],
                PnL[Position Level1],
                PnL[Position Level2],
                PnL[Sortowanie level 2]
            ),
            PnL[Position Level1] = "Sales revenue",
            PnL[Position Level2] = "Online"
        ),
        0
    ) * 100,
    IF (
        SELECTEDVALUE ( PnL[Position Level2] ) = "Offline"
            && SELECTEDVALUE ( PnL[Position Level1] ) = "COGS",
        DIVIDE (
            [P&L AC],
            CALCULATE (
                [P&L AC],
                ALL (
                    PnL[Sortowanie level 1],
                    PnL[Position Level1],
                    PnL[Position Level2],
                    PnL[Sortowanie level 2]
                ),
                PnL[Position Level1] = "Sales revenue",
                PnL[Position Level2] = "Offline"
            ),
            0
        ) * 100,
        IF (
            SELECTEDVALUE ( PnL[Position Level2] ) = "Online"
                && SELECTEDVALUE ( PnL[Position Level1] ) = "Gross margin",
            DIVIDE (
                [P&L AC],
                CALCULATE (
                    [P&L AC],
                    ALL (
                        PnL[Sortowanie level 1],
                        PnL[Position Level1],
                        PnL[Position Level2],
                        PnL[Sortowanie level 2]
                    ),
                    PnL[Position Level1] = "Sales revenue",
                    PnL[Position Level2] = "Online"
                ),
                0
            ) * 100,
            IF (
                SELECTEDVALUE ( PnL[Position Level2] ) = "Offline"
                    && SELECTEDVALUE ( PnL[Position Level1] ) = "Gross margin",
                DIVIDE (
                    [P&L AC],
                    CALCULATE (
                        [P&L AC],
                        ALL (
                            PnL[Sortowanie level 1],
                            PnL[Position Level1],
                            PnL[Position Level2],
                            PnL[Sortowanie level 2]
                        ),
                        PnL[Position Level1] = "Sales revenue",
                        PnL[Position Level2] = "Offline"
                    ),
                    0
                ) * 100,
                DIVIDE (
                    [P&L AC],
                    CALCULATE (
                        [P&L AC],
                        ALL (
                            PnL[Sortowanie level 1],
                            PnL[Position Level1],
                            PnL[Position Level2],
                            PnL[Sortowanie level 2]
                        ),
                        PnL[Position Level1] = "Sales revenue"
                    ),
                    0
                ) * 100
            )
        )
    )
)
2

Sending out the signal for the Antman, @AntrikshSharma …

antman

1 Like
3

@Harris Since you are repeating same thing again and again and only changing one statement in CALCULATE, just create a table with ADDCOLUMNS and add new column for P&L AC and then based on the condition use a FILTER to filter the result and maybe create 3-4 variable for the DIVIDE part, make sure that the ADDCOLUMNS part is computed in a variable before IF or you can use ADDCOLUMNS inside IF with IF.EAGER.

1 Like
4

@AntrikshSharma There is a problem because I am using a live connection and I cannot create columns

5

@Harris Why wasn’t this included in your original post?

2 Likes
6

@AntrikshSharma Sorry, I thought it didn’t matter

1 Like
7

@Harris I meant using ADDCOLUMNS inside the measure, like a virtual table.