Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Midhunc22
Regular Visitor

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 3
ryan_mayu
Super User
Super User

@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])

1.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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])

@Midhunc22 

pls try this

 

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors