Forum Discussion

LiborK's avatar
LiborK
Frequent Visitor
4 years ago
Solved

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 Colum...
running total
  • SpartaBI's avatar
    SpartaBI
    4 years ago

    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


         

  • v-cazheng-msft's avatar
    v-cazheng-msft
    4 years ago

    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

SpartaBI's avatar
SpartaBI
Icon for Community Champion rankCommunity Champion
4 years ago

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


     

  • LiborK's avatar
    LiborK
    Frequent Visitor
    4 years ago

    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...

    • SpartaBI's avatar
      SpartaBI
      Icon for Community Champion rankCommunity Champion
      4 years ago

      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.

      • LiborK's avatar
        LiborK
        Frequent Visitor
        4 years ago

        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: