Forum Discussion
Cumulative Sum Dynamic Requirement - Help
Hi, I am unable to achieve Dynamic Cumulative Sum with simple DIM_PRODUCT and Revenue Tables. Please find below link to PBIX, the Rank works the way I want because it respects the filters but t...
Hi archerjayden
I have created two measures which are 10 times faster than the ones you have. However they are still slow if you don't select anything in the slicer. https://we.tl/t-D7E7z2NQ8K
Running Revenue 1 =
IF (
NOT ISBLANK ( [SalesRevenue] ),
VAR CurrentRank = [ProductRankCol]
VAR SelectedProducts = ALLSELECTED ( DIM_PRODUCT[SKU] )
VAR ProductsAndSales = ADDCOLUMNS ( SelectedProducts, "@Revenue", [ProductRevenueMSR] )
VAR ProductsWithSales = FILTER ( ProductsAndSales, [@Revenue] <> BLANK ( ) )
VAR TopNProducts = TOPN ( CurrentRank, ProductsWithSales, [@Revenue] )
RETURN
SUMX ( TopNProducts, [@Revenue] )
)Running Revenue 2 =
IF (
NOT ISBLANK ( [SalesRevenue] ),
VAR CurrentRank = [ProductRankCol]
VAR SelectedProducts = ALLSELECTED ( DIM_PRODUCT )
VAR ProductsWithSales = FILTER ( SelectedProducts, DIM_PRODUCT[ProductRevenueCol] <> BLANK ( ) )
VAR Products = SUMMARIZE ( ProductsWithSales, DIM_PRODUCT[SKU] )
VAR ProductsAndSales = ADDCOLUMNS ( Products, "@Revenue", [ProductRevenueMSR] )
VAR TopNProducts = TOPN ( CurrentRank, ProductsAndSales, [@Revenue] )
RETURN
SUMX ( TopNProducts, [@Revenue] )
)
tamerj1 wrote:However they are still slow if you don't select anything in the slicer.
Thank you for providing these measures they work awesome, really great !
It is taking 4.68 Mins to render the visual without any filter selected, can anything be done about this?
archerjayden
Cumulative totals based on dynamic ranking with 300,000+ products all present in one table visual is very heavy and expensive calculation. For each row in the table visual, the whole table will be evaluated. I don't believe anything better can be achieved.