Forum Discussion

Midhunc22's avatar
Midhunc22
Regular Visitor
3 years ago

Running Total based on Dimension level

Hello,

     I am trying to get the Running total based on ServiceID But getting an error. Below is my table data . I

Group     Service ID     Amount

   A               1                $2

   A               4                $1

   A               2                $3

   B               2                $1

   B               6                $4

When i try to create Rank based on the below dax getting an error "A single value for column ' Service ID ' in table 'Sampledata' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result"

Rank = RANKX(SampleData,SampleData[Service ID],,ASC,Dense)

for the Running total i am using below dax , But the Earlier function does not recognise the any column from the table.

 

RunningTotal =
CALCULATE (
    SUM ( SampleData[Amount] ),
    ALLEXCEPT ( SampleData, SampleData[Group] ),
    SampleData[Rank] <= EARLIER ( SampleData[Rank] )
)

 

Please help me know , what went wriong here. ? 

TIA

Midhun

3 Replies

  • Midhunc22 

    why you want to create rank column?

    is this what you want?

    running total = sumx(FILTER('Table','Table'[Group]=EARLIER('Table'[Group])&&'Table'[Service ID]<=EARLIER('Table'[Service ID])),'Table'[Amount])

    • Midhunc22's avatar
      Midhunc22
      Regular Visitor

      Hi ryan_mayu  ,

          I have tried this DAX as well. but Earlier function does not getting any columns its giving an error as Earlier row context which does not exist .

       

      running total = sumx(FILTER(Sampledata,Sampledata[Group]=EARLIER(group)&&Sampledata[ Service ID ]<=EARLIER(Service)),Sampledata[Amount])

      • ryan_mayu's avatar
        ryan_mayu
        Super User

        Midhunc22 

        pls try this

         

        running total = sumx(FILTER(Sampledata,Sampledata[Group]=EARLIER(Sampledata[Group])&&Sampledata[ Service ID ]<=EARLIER(Sampledata[ Service ID ])),Sampledata[Amount])