topic Re: Running Total in DAX Commands and Tips

Running Total

LiborK — Sat, 14 May 2022 11:49:36 GMT

HHello, I don't know, how to write in dax Running Total Query for below situation:

I only find examples for situations where the Count ID is not similar in one Column. So I get values in Column Actual Running Total. Do you know how to write it?

Thank you in advance.

Re: Running Total

tamerj1 — Sat, 14 May 2022 12:03:11 GMT

Hi LiborK

would you please clarify further?

Re: Running Total

SpartaBI — Sat, 14 May 2022 12:14:12 GMT

LiborK yes, but you need to choose which one is your 2nd important column for the sorting (the 1st is of course the [Count ID] )
Let's say it's the [ID] column and let's say higher ID -> Higher rank
So, first, create this calculated column:

'Table'[Sorting] = 'Table'[Count ID] * ROUND(MAX('Table'[ID]), -1) + 'Table'[ID].

Then create this Count RT column:

'Table'[Count RT] = VAR _current_rank = 'Table'[Sorting] RETURN SUMX(FILTER('Table, 'Table'[Sorting] <= _current_rank), 'Table'[% ID])

Showcase Report – Contoso By SpartaBI

Re: Running Total

LiborK — Sun, 15 May 2022 19:12:46 GMT

Hi, thank you for you answer, but I am not able to choose

'Table'[Sorting] = 'Table'[Count ID] * ROUND(MAX('Table'[ID]), -1) + 'Table'[ID].

'Table' [ID] in second step after "+" it says Cannot find name 'ID'.

If its helps Count IDs, Total Count Of IDs, %ID, Actual Running Sum are measures...

Re: Running Total

SpartaBI — Sun, 15 May 2022 19:35:17 GMT

LiborK you have that column in your model? 'Table'[ID]?

Also, the picture you sent is a matrix visual where you put that column on the rows of a matrix and all the rest are measures? Is all coming from the same table or there are several tables involved? Best if you can share the file but maybe will be able to answer without, just with answers to my questions.

Re: Running Total

LiborK — Mon, 16 May 2022 08:53:07 GMT

Yes, the column is in my model...I am able to find it for the green colored part, but not for the red colored part:

'Table'[Sorting] = 'Table'[Count ID] * ROUND(MAX('Table'[ID]), -1) + 'Table'[ID], only column which is not measure is "ID" from the example above, but it is distinct like in pivot table. Original table looks like this example:

Re: Running Total

SpartaBI — Mon, 16 May 2022 10:10:33 GMT

LiborK
So, no calculated columns. Create this measure:

Count RT = VAR _current_count = [COUNT ID'S] VAR _current_id = SELECTEDVALUE('Table'[ID]) VAR _max_customer_id = ROUND(CALCULATE(MAX('Table'[ID]), ALLSELECTED('Table'[ID]),-1) VAR _ranking_table = ADDCOLUMNS( ALLSELECTED('Table'[ID]), "@%ID", [% ID], "@Ranking", [COUNT ID'S] * _max_customer_id + 'Table'[ID] ) VAR _current_rank = _current_sales * _max_customer_id + _current_id VAR _result = SUMX( FILTER( _ranking_table, [@Ranking] >= _current_rank ), [@%ID] ) RETURN _result

Showcase Report – Contoso By SpartaBI

Re: Running Total

LiborK — Mon, 16 May 2022 11:25:46 GMT

Thank you so much for your help, but it is still not working...I will try to describe it in different way.

I have just table with this column "ID" and values in this column are with structure like in picture below ( for example 13, can be there multiple times) and I dont have there any other measures.

And my task is to create table where in first column will be ID (for each row unique ID) and the rest of the column will be like in picture 2

and I am able to create measures for column B-E, but I am not able to create measure for column F.

VAR _ranking_table =
ADDCOLUMNS(
ALLSELECTED('Table'[ID]),
"@%ID"(parametr is not in correct type), [% ID](I am not able to find the measure, it provide me just standard column from model),
"@Ranking", [COUNT ID'S] * _max_customer_id + 'Table'[ID]
)

Re: Running Total

SpartaBI — Mon, 16 May 2022 11:36:21 GMT

LiborK are you trying to create a matrix visual or a calculated table?

Re: Running Total

SpartaBI — Mon, 16 May 2022 11:39:14 GMT

Also, look at my code ID is a temporary column I created. I'm adding @ to distinguish it from regular model columns

Re: Running Total

LiborK — Mon, 16 May 2022 11:44:14 GMT

Table

Re: Running Total

SpartaBI — Mon, 16 May 2022 11:52:03 GMT

LiborK ok, then my measure should work. Show me the fields you put on the buckets and also the result of them and of my measure. If you get an error in my measure, please verify that all the column names and measures are correct

Re: Running Total

v-cazheng-msft — Wed, 18 May 2022 01:08:05 GMT

Hi LiborK,

You may try this solution.

Here are the Measures created.

Count ID = CALCULATE(COUNT('Table'[ID]),ALLEXCEPT('Table','Table'[ID])) Total Count of IDs = CALCULATE(COUNT('Table'[ID]),ALL('Table')) %ID = DIVIDE([Count ID],'Table'[Total Count of IDs]) Wished Running Sum = CALCULATE([%ID],FILTER(ALL('Table'),'Table'[RANK]>=MAX('Table'[RANK])))

Here are the Calculated columns created.

DistinctCountID =CALCULATE(COUNT('Table'[ID]),FILTER(ALL('Table'),'Table'[ID]=EARLIER('Table'[ID]))) RANK = VAR MaxID = CALCULATE ( MAX ( 'Table'[ID] ), ALLEXCEPT ( 'Table', 'Table'[DistinctCountID] ) ) VAR Addition = ROUND ( 'Table'[%ID], 5 ) * 100000 RETURN IF ( 'Table'[ID] < MaxID, Addition + MaxID, Addition )

Then, the result look like this.

Also, attach the pbix file as reference.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!

Best Regards,

Community Support Team _ Caiyun

Re: Running Total

LiborK — Tue, 17 May 2022 10:05:43 GMT

It works thank you so much!