Forum Discussion

sharong's avatar
sharong
Icon for Helper I rankHelper I
3 years ago

Running Total for non date column

I am trying to do running total on non date column. I am trying to show it in a table visual. I have 18k rows.
Following the below dax, its working for me if i limit rows to first 1000 rows. But for entire data, its keeps loading and not coming up.
Can you help me in optimizing the dax measure or is there any other way to achieve this.

 

My table visual would have the following columns:
CUSTOMER_NO, CUSTOMER_NAME, CODE, COUNTRY, UNIT, Rank, Total receivables, Running total

 

The following is the measure I have used:
Product Running Total =

var pdtrank = RANKX(ALLSELECTED(table[CUSTOMER_NO], table[CUSTOMER_NAME], table[CODE], table[COUNTRY], table[UNIT]), [totalReceivables], , DESC, Dense)

var runningtotal =
CALCULATE([totalReceivables],
FILTER(ALL(table[CUSTOMER_NO], table[CUSTOMER_NAME], table[CODE], table[COUNTRY], table[UNIT]),
pdtrank >= RANKX(ALLSELECTED(table[CUSTOMER_NO], table[CUSTOMER_NAME], table[CODE], table[COUNTRY], table[UNIT]), [totalReceivables], , DESC, Dense)
) )
return

IF(
[totalReceivables] <> BLANK(),
RunningTotal
)

 

Below is the error I m getting

If I dont limit the data to first 1000 rows, then its keeps loading and never gives the result

 

12 Replies

  • Hi sharong ,

     

    I think the issue is loading the measure. The measure seems bit complex. 

    I suggest you to use SUMX and minimize the formula.

     

    Regards,

    Nikhil Chenna

     

    Appreciate with a Kudos!! (Click the Thumbs Up Button)
    Did I answer your question? Mark my post as a solution!

    • sharong's avatar
      sharong
      Icon for Helper I rankHelper I

      Hi,
      Can you provide me the minimized formula using sumx . will try it out

      • NikhilChenna's avatar
        NikhilChenna
        Icon for Skilled Sharer rankSkilled Sharer

        Hi sharong ,

         

        Can you only share you sample table as well as a expected output table for it. so that it will be easy for me to test it out too.

         

        Regards,

        Nikhil Chenna

         

        Appreciate with a Kudos!! (Click the Thumbs Up Button)
        Did I answer your question? Mark my post as a solution!