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 providing my sample "PBIX" file here.

 

I got like below snapshot

 

 

Can anyone help on this.... Thanks in advance

 

Need Help
  • 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.

     

     

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Have you tried this Venu?

     

    • venug20's avatar
      venug20
      Resolver I
      7 years ago

      Anonymous

       

      i think you are not understand my requirement, i have already got TOP 10 values. 

       

      beside the column i need running total for this top 10 values....

  • venug20's avatar
    venug20
    Resolver I
    7 years ago

    Hi everyone,

     

    Anyone help on this.... Thanks in advance....

    • parry2k's avatar
      parry2k
      Super User
      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.