cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
archerjayden
Helper I
Helper I

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 the cumulative sum just doesnt run at all. What seems to be the issue and may be tweak my dax to make it work , I am unable to figure this one out for 3 days now. I am trying to embed ABC classification into dimension table so my ABC classificaiton is dynamic based on highest revenue and filters selected from dimension table.

@AntrikshSharma,@Greg_Deckler@amitchandak  can you please help 

 

Thanks a lot

https://ufile.io/i3ikj4bs 

3 REPLIES 3
tamerj1
Super User
Super User

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

1.png2.png

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. 

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors