Forum Discussion
Running Total
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.
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 _resultHi 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
13 Replies
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])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...
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.
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
It works thank you so much!
