Help with optimizing DAX measure - Income statement

[Nicpet0]

Hi community!

I was hoping that someone out there can help me with some DAX or at least put me in the right direction. I will spend a bit time time to explain my set up and what i am trying to achieve.

Essentially i am trying to build an income statement in Power BI. In my semantic model i have G L Accounts that i am using to create a sharepoint lists, linking the accounts to subcategories and categories (this is actually also in my model, however i needed a custom mapping so thats why i have it in SharePoint). However, there are some categories that i dont have in my model such as Gross Profit, Overheads, EBITDA or EBIT, that i have tried to construct in my DAX measure as you can see below. Those names i have created in the SharePoint List so that the model knows them without them having any data linked to them. The code example i provided is for Last year same period, but i have similar constructed measures just for other scenarios like YTD and FY. I have in my report a matrix table that contains the three levels; category, subcategory and account item and the categories are returned as expected with the correct values, but the issue i am running into is that when i expand to level 2 the performance is significalty slower and on level 3 it becomes even worse. So my 1 million dollar question is if there are a way i can optimize the code? 

Bonus info: the performance is in fact not a big issue on all three level, however i have noticed whenever i have enabled data bars which i use for variance columns the performance becomes a big issue for me when expanding the rows.

Thank you!

P&L LY =

VAR _Category  = SELECTEDVALUE('G L Account Category SharePoint'[G L Account Category])

RETURN

SWITCH(

    TRUE(),

    _Category = "Gross Profit",

        CALCULATE(

            [ACT LY],

            REMOVEFILTERS('G L Account Category SharePoint'),

                'G L Account Category SharePoint'[Index] < 3

        ),

    _Category = "Total Overheads",

        CALCULATE(

            [ACT LY],

            REMOVEFILTERS('G L Account Category SharePoint'),

                'G L Account Category SharePoint'[Index] < 7 && 'G L Account Category SharePoint'[Index] > 3

        ),

    _Category = "Total Personnel Costs",

        CALCULATE(

            [ACT LY],

            REMOVEFILTERS('G L Account Category SharePoint'),

                'G L Account Category SharePoint'[Index] < 10 && 'G L Account Category SharePoint'[Index] > 7

        ),

    _Category = "EBITDA",

        CALCULATE(

            [ACT LY],

            REMOVEFILTERS('G L Account Category SharePoint'),

                'G L Account Category SharePoint'[Index] < 13

            ),

    _Category = "EBIT",

        CALCULATE(

            [ACT LY],

            REMOVEFILTERS('G L Account Category SharePoint'),

                'G L Account Category SharePoint'[Index] < 15

            ),

    [ACT LY]

)

[johnt75]

We'll need more info before we can offer any suggestions. Ideally you could share a PBIX uploaded to Google Drive, OneDrive etc, but if you can't share that due to data sensitivity I think we'd need a diagram view showing all the tables involved in the calculation and the relationships between them, which fields you have in your matrix and which tables those come from, and finally we'd need the definition of [ACT LY] and any measures that depends on.

[Nicpet0 · Accepted solution]

Hi,

I found my way around it. My problem seems that when i expanded a row a lot of the G L Accounts were being re-calculated multiple times, for instance Revenue accounts goes into revenue, gross profit, EBITDA and EBIT but using INSCOPE allowed me to not allow any hierarchy beneath the categories i defined in my code and so they are being calculated at a top level but not again when i expand a row