Need help with complex ranking/cumulative scenario

Power BI
22

This thread is getting quite long and I know Brian has already spent a huge amount of time on this.

Any ranking formula in Power BI either TOPN and RANKX has poor performance over large amount of data.

The only way to tried to improve this is to change the virtual table size that the ranking iterates through in the formula. Or alternatively attempt to aggregate your data up into a new physical table, then work out the ranking over that. These are really your two options that I would explore further for this.

Max Calc Range Grouped = 

CALCULATE(
    MAX( Data[Index] ),
    TOPN(
        1,
        FILTER(
            ALLEXCEPT(
                Data,
                Data[YEAR_WEEK],
                Data[PRODUCT_CATEGORY]),
            [Cumulative Volume Grouped] > [Total Grouped Volume % Applied]
        ),
        Data[Index],
        ASC
    ),
    ALLEXCEPT(
        Data,
        Data[YEAR_WEEK],
        Data[PRODUCT_CATEGORY]
    )
)

This most likely is were the slowness is occurring. I would also attempt to break this down and look at it peice by peice, to see which exact part of the formula is causing things to go a bit slower.

image

I would also say this cumulative total could be the slow part here as well over a large dataset.

See how you go with these ideas.

Thanks
Sam

1 Like
23

The performance analyzer is a great tool to use when breaking down each part of formulas. Give this a go as well

image
image.png1004×283 19.3 KB

24

Hi @Luukv93, a response to this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!

25

Unfortunately I did not get it working in Power BI due to performance issues. I used Python to aggregate the data the way you did in the post and used Power BI to visualize the result to my end user.

Really recommend the pandas and numpy libraries in Python for advanced ETL

1 Like