Forum Discussion

tecumseh's avatar
tecumseh
Resolver III
1 year ago
Solved

Speed Up Cumulative Calculation

Hi,

Is it possible to speed up this cumulative calculation?

 

Cumulative Revenue = 
IF(
ISINSCOPE(Source[Customer]), 
CALCULATE(
[Net_Revenue],
TOPN([Rank],
ALLSELECTED(Source[Customer]),
[Net_Revenue])
),
[Total Net Revenue]
)

 Thanks,

-w

Need Help
  • AlexisOlson's avatar
    AlexisOlson
    1 year ago

    See if this helps:

    Cumulative Revenue =
VAR _Rank = [Rank]
VAR _CustRev_ =
    ADDCOLUMNS (
        ALLSELECTED ( Source[Customer] ),
        "@NetRev", [Net_Revenue]
    )
VAR _TopNRev =
    SUMX (
        TOPN ( _Rank, _CustRev_, [@NetRev] ),
        [@NetRev]
    )
RETURN
    IF (
        ISINSCOPE ( Source[Customer] ),
        _TopNRev,
        [Total Net Revenue]
    )

2 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    1 year ago

    See if this helps:

    Cumulative Revenue =
VAR _Rank = [Rank]
VAR _CustRev_ =
    ADDCOLUMNS (
        ALLSELECTED ( Source[Customer] ),
        "@NetRev", [Net_Revenue]
    )
VAR _TopNRev =
    SUMX (
        TOPN ( _Rank, _CustRev_, [@NetRev] ),
        [@NetRev]
    )
RETURN
    IF (
        ISINSCOPE ( Source[Customer] ),
        _TopNRev,
        [Total Net Revenue]
    )