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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
pejczi
Frequent Visitor

Calculate sum of all previous indexes

Hello,

 

Let's say that I have a table like that :

 

Index   Value

1           90

2           120

3           140

4           150

 

In the visualisation, I am making a slicer with Indexes (so I can choose from 1 to 4)

 

What I want to do, is when I select 3, I am calculating the sum of all values below and including 3 (in the example it would be 90+120+140).

 

Any solutions? I was trying various calculate() + filter() options, but none of them worked correctly.

1 ACCEPTED SOLUTION
popov
Resolver III
Resolver III

Hello, @pejczi

Please, try this formula:

Cumulative Total =
CALCULATE (
    SUM ( Table[Value] ),
    FILTER ( ALL ( Table[Index] ), Table[Index] <= MAX ( Table[Index] ) )
)

View solution in original post

3 REPLIES 3
JulioGadioli
Frequent Visitor

I want to collaborate with my code that fetches the remaining value (sum) by the previous index.

 

Create column

Indice = RANKX(ALL(ParcelaLista_Filtrado),ParcelaLista_Filtrado[Data da Base],, ASC, DENSE )

 

And 

Create a measure

Valor Anterior =
 IF (SELECTEDVALUE(ParcelaLista_Filtrado[Indice] ) <> 1,
 CALCULATE(SUM(ParcelaLista_Filtrado[Valor restante]),FILTER(all('ParcelaLista_Filtrado'),'ParcelaLista_Filtrado'[Indice]<= MAX('ParcelaLista_Filtrado'[Indice])-1)),
  0
 )

Thanks popov I want to your colaboration, by ALL () and ...<=MAX helped me a lot.
popov
Resolver III
Resolver III

Hello, @pejczi

Please, try this formula:

Cumulative Total =
CALCULATE (
    SUM ( Table[Value] ),
    FILTER ( ALL ( Table[Index] ), Table[Index] <= MAX ( Table[Index] ) )
)

pejczi
Frequent Visitor

Thank you, it works after adjusting to my case (some of features stopped working, but I need to adjust relations a little bit, but generally it is okay :-))

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.