Ranking and Rolling Sums for huge dataset

[y5famfnatudu]

Good morning,

I have a PBI report where raning and rolling sums are working fine, with about 25K records.

But when my table contains 180K records, then ranking and rolling sums time-out and don't work any more.

I would appreciate so much if you can guide me on solving this issue.

The Data sample and the PBI can be found here:

PBI 

Data 

Thank you so much.

Simon

[v-henryk-mstf]

Hi @y5famfnatudu ,

Rolling Sums for what?

Best Regards,
Henry

[y5famfnatudu]

Hi @v-henryk-mstf 

Rolling sums for the revenue.

Best regards,

Simon

[v-henryk-mstf]

Hi @y5famfnatudu ,

I need to confirm with you that you want to add up the sums in order according to the ranking order, right? Looking forward to your reply.

Best Regards,
Henry

[y5famfnatudu]

Hi @v-henryk-mstf ,

Exactly, the ranking is based on the desecnding order of the Revenue.

Best regards,

Simon

[y5famfnatudu]

I have changed the Ranking DAX formula to the following:

_ContactRank2 := 
RANKX (
    ALLSELECTED ( Contact[Debitor Nr] ),
    CALCULATE (
        SUM ( Sales[Revenue] ),
        ALLEXCEPT (
            Contact,
            Contact[Debitor Nr]
        )
    )
)

 Now, Ranking is working, but the Rolling Sum is still not

[y5famfnatudu]

@DataInsights Any ideas here? that would be so much appreciated.

[DataInsights · Accepted solution]

@y5famfnatudu,

Here's a solution using calculated columns, but the downside is they don't recognize user selections (e.g., slicer). Thus, the ranking is based on all revenue, not revenue for a specific date range. Running totals in measures can have performance issues as data volume increases.

In the Contact table, create these calculated columns:

Total Revenue = CALCULATE( [_Revenue], RELATEDTABLE ( Sales ) )

Contact Rank = RANKX ( Contact, Contact[Total Revenue],, DESC, Dense )

Revenue Rolling = 
VAR vRank = Contact[Contact Rank]
VAR vResult =
    CALCULATE (
        SUM ( Contact[Total Revenue] ),
        ALL ( Contact ),
        Contact[Contact Rank] <= vRank
    )
RETURN
    vResult

Create measure:

_Revenue Rolling = SUM ( Contact[Revenue Rolling] )