Forum Discussion

venug20's avatar
venug20
Resolver I
7 years ago
Solved

Top 10 Values with Running Total

Hi everyone,   I have two tables "Product" & "SalesFact"....   I want to show top 10 products by revenue.... and also show in another column running total for those top 10 values.   I am provid...
  • parry2k's avatar
    parry2k
    7 years ago

    Hi venug20

     

    try this, first create another measure for top10 sales, I named it top10sales, reason I added this new measure because total line of top10 items is not showing correctly:

     

    Top10Sale2 =
     var myRank = [Rank] 
    RETURN 
    SUMX( 
    VALUES(bi_product[Product]), 
    IF(myRank <=10,  [Top10Sale] )
     )

    We will use top n to get the list of products based on rank and then sum it up, if condition is to  avoid showing all the product and remove running total in the "total" line of the table.

     

    Top10RunningTotal = 
    IF( [Rank] <= 10 && HASONEFILTER( bi_product[Product] ), 
    CALCULATE( [Total Sales], 
    TOPN( [Rank], ALL(bi_product[Product] ), [Total Sales] )
     )
     )

    Hope it is helpful,you can take it from here.