Performance issue with card visual showing average growth rate with field parameters

[scoobydoo]

Dear community,

I am working on a card visual that shows the growth rate of three categories: Actual, Forecast, and Budget. It is dynamic and uses several field parameters. You can find the pbix-file with some random data here .

In general, this all works fine, except of the performance of the average growth rate. The problem is that the visual takes too long to load for the Budget growth rate. As background information: Its calculation is a little bit more complex than the Actual and Forecast growth rates, because the Actual and Forecast data are monthly and only for 2023, while the Budget data is yearly for 2024-2026. So I split the yearly Budget values into months using the same pattern as the monthly Budget data in 2023.

The measure that I use to calculate the average growth rate for the Budget category is this:

Avg Growth Rate BUD = 
VAR SelectedTimePeriod = MAX( Trend_TimePeriods[Time periods] )

RETURN
    IF(
        SelectedTimePeriod = "Quarter",
        CALCULATE(
            AVERAGEX(
                VALUES(Datetable[QuarterYear]),
                IF(
                    NOT ISBLANK([Growth Rate BUD]),
                    [Growth Rate BUD]
                )
            )
        ),
        CALCULATE(
            AVERAGEX(
                VALUES(Datetable[MonthYear]),
                IF(
                    NOT ISBLANK([Growth Rate BUD]),
                    [Growth Rate BUD]
                )
            )
        )
    )

This measure depends on another measure that calculates the monthly growth rate for the Budget category. This measure is more complex and uses several variables and conditions. You can see the full code of this measure and the other measures that it refers to in the pbix-file:

Dropbox 

I would appreciate any feedback and help on this!

Many thanks in advance 🙏

[HotChilli]

"As for the dimension table for the "version", I'm afraid this is not possible" - it will be possible.  Get the distinct values in the dimension table and create the relationship 1:m

--

For the measure above, delete the CALCULATE, it's not required.

----

Interesting that you say this is your first powerbi project.  Did you write this from scratch? It's complicated stuff

[scoobydoo]

Oh, yes you're right, I'll delete the calculate - Thank you! Also I will set up the new table. May I ask about the theory or the implications behind the new table? I hadn't done this so far because I thought in this case it would just replace the values "BUD", "ACT", etc. with a numeric key. Does replacing the text with a key already make a big difference to the performance? 

---

Since this is my first project: I've been working on this already since July, so I've had plenty of time to watch tons of YouTube videos, read SQLBI articles, and read the posts in this community. And I use ChatGPT almost daily to check my DAX or to help me with problems.

Without all this stuff I would never have been able to do this from scratch, as the other pages also contain complicated things. But it's fun! I really enjoy it and learning from people like you is extremely helpful!

[HotChilli]

I've had a look at the file and there's a lot going on.

My first question would be : is all the handling for blank values of the measure really required? I suppose it has been included for a good reason but it looks unnecessary. Can you remove the calculate and the blank handling? Maybe you can have a look at that.

There also are a lot of measures which filter the fact table.  Can you introduce a dimension table for version (connected 1:m with the fact table) and filter that instead? for example:

Sales for BUD = 
//CALCULATE ( SUM ( Data[Sales] ), Data[Version] = "BUD" )
CALCULATE ( SUM ( Data[Sales] ), DimVersion[Version] = "BUD" )

[scoobydoo]

Thank you! 
Regarding your first question, the last days I tried debugging every single step of the calculations, and the biggest difference actually occurred when I deleted the blank value handling. Now it just looks like this:

Avg Growth Rate BUD = 
VAR SelectedTimePeriod = MAX( Trend_TimePeriods[Time periods] )

RETURN
    IF(
        SelectedTimePeriod = "Quarter",
        CALCULATE(
            AVERAGEX(
                VALUES( Datetable[QuarterYear] ), 
                [Growth Rate BUD]
            )
        ),
        CALCULATE(
            AVERAGEX(
                VALUES( Datetable[MonthYear] ),
                [Growth Rate BUD]
            )
        )
    )

The update still takes a little while, but it is now much faster and in an acceptable range. However, I will still try to simplify the other actions to speed it up.

As for the dimension table for the "version", I'm afraid this is not possible (if I'm correct) because there would be a many-many relationship. All of the products included have values (sales, gross profit, units) for each of the versions (e.g. ACT, FC and BUD).

By the way, this is my first Power BI project, so I'm still learning a lot and apologize for any mistakes that would make an expert cringe 😉