topic Re: Running Total for non date column in DAX Commands and Tips

Running Total for non date column

sharong — Tue, 29 Nov 2022 07:52:51 GMT

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

Re: Running Total for non date column

NikhilChenna — Tue, 29 Nov 2022 12:36:02 GMT

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!

Re: Running Total for non date column

sharong — Tue, 29 Nov 2022 12:41:10 GMT

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

Re: Running Total for non date column

NikhilChenna — Tue, 29 Nov 2022 13:18:51 GMT

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!

Re: Running Total for non date column

sharong — Tue, 29 Nov 2022 13:51:19 GMT

This is the existing table i have. I want a new column "Running sum" of receivables based on rank st.
Total number of rows in dataset is 18079 rows.

Re: Running Total for non date column

NikhilChenna — Tue, 29 Nov 2022 14:05:21 GMT

Hi sharong ,

Create the below measure ,

Running total sales =

VAR IndexRank = [Rank ST]

RETURN

CALCULATE (

SUM ( 'table1'[Receivables]),

FILTER (

ALL ( 'table1'[customer_no] ),

IndexRank

>= RANKX ( ALLSELECTED ( 'table1'[customer_no] ), [sum],, ASC, DENSE )

)

Regards,

Nikhil Chenna

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

Re: Running Total for non date column

sharong — Tue, 29 Nov 2022 15:55:28 GMT

Hi NikhilChenna
This too is not working. The table visual keeps loading. Can you please help me out. 18k rows is a small dataset. Dont know why it keeps loading. Please help me out. Struck with this for last 1 week

Re: Running Total for non date column

NikhilChenna — Wed, 30 Nov 2022 05:41:37 GMT

Hi sharong , Is it possible can you share you pbix file.

Re: Running Total for non date column

sharong — Thu, 01 Dec 2022 12:48:09 GMT

amitchandak can you please help me in this. I am not getting a proper reply from anyone

https://drive.google.com/file/d/1IRw6GIiZyFeCXMb3-ARe_WQh86Yva7zf/view?usp=sharing

If I drag the running sum measure into the table visual, it keeps loading and not coming up for 18k rows. But for less rows like first 1000 rows, its working. Can you please provide me with a solution on urgent basis ?

Re: Running Total for non date column

NikhilChenna — Wed, 30 Nov 2022 08:59:55 GMT

Hi sharong ,

Is this the output you want ?

Regards,

Nikhil Chenna

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

Re: Running Total for non date column

sharong — Wed, 30 Nov 2022 09:09:56 GMT

Yeah but why every value in running sum column is same. I want a cumulative sum

Re: Running Total for non date column

sharong — Thu, 01 Dec 2022 03:43:51 GMT

Hi NikhilChenna

I tried using the below codes. It takes some 4-5 minutes to load the table. Is there any way of optimizing the below code. Can you help me out.

TestRank = IF(ISBLANK([Total Receivables]), BLANK(),

RANKX (

SUMMARIZE (

ALLSELECTED ( Table ),

Table[CUSTOMER_NO], Table[CUSTOMER_NAME], Table[COMPANY_CODE], Table[COUNTRY], Table[BUSINESS_UNIT]

CALCULATE ( SUM ( Table[Total Receivables] ) ),

DESC,

DENSE

))

-----------------------------

Test Running Total =

VAR CurrentRank = [TestRank]

VAR Summarizetable =

CALCULATETABLE (

SUMMARIZE ( Table, Table[CUSTOMER_NO], Table[CUSTOMER_NAME], Table[COMPANY_CODE],Table[BUSINESS_UNIT],Table[COUNTRY] ),

ALLSELECTED ( Table )

)

VAR small =

ADDCOLUMNS ( Summarizetable, "@Sales", [Total Receivables], "@Rank", [TestRank] )

VAR Result =

SUMX ( FILTER ( small, [@Rank] <= CurrentRank ), [@Sales] )

RETURN

Result

Re: Running Total for non date column

sharong — Thu, 01 Dec 2022 12:37:13 GMT

hi NikhilChenna 7is there any update on this issue?