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
OscarSuarez10
Helper III
Helper III

Cumulative Sum problem

Hello I want to calculate the cumulative sum of UniquePageViews, to get this results:

DateUniquePageViewsRunning Total Sume
155
22530
370100
48108
13232
2133
32558
43694
12568
24365
32287
41097

 

As you can see column dates has a repeated sequence of dates.

Im using the following formula to cerate a colum to do this but Im getting a wrong cumulative sum in the column Running Total Sume as you can see there:

problema suma acumulada año repetido.JPG

I dont know why it doesnit work for repeated dates, 

Can you help me?

 

1 ACCEPTED SOLUTION
sturlaws
Resident Rockstar
Resident Rockstar

Hi @OscarSuarez10 

 

Let us take a look at what happens when you look at Hoja[Date]=1, with special attention to this part:

Hoja[Date] <= Earlier(Hoja[Date])

When you look at a row where [Date]=1, this code says find all rows where [Date] is smaller or equal to 1. Hence, from you example, it sums 5, 32 and 25 = 62.

So how to fix this? You are relying on the sorted order of your table. Since you are looking at UniquePageViews, perhaps you have a uniquePageID available? If you have, you could change your code to this:

Running Total Sume =
CALCULATE (
    SUM ( Hoja3[UniquePageViews] );
    FILTER (
        ALLSELECTED ( Hoja3 );
        Hoja3[date] <= EARLIER ( Hoja3[Date] )
            && Hoja3[UniquePageID] = EARLIER ( [UniquePageID] )
    )
)

I have rewritten you code using the ALLSELECTED-function, although ALLSELECTED is a particular difficult function to master. I can't see it has any uses for you here, and unless you have found that it has a purpose in your code, I would recomend that you change it to use the ALL-function.

Now, if don't have a something like a uniquePageID available, we need to rely on the order of Date in the input, and generate a uniqe ID on our own. There are a few steps to this, so instead of describing them, I have created a demo report

Go to Power Query/Edit queries to see how this is done.

 

Cheers,
sturla

 

If this post helps, then please consider Accepting it as the solution. Kudos is also nice.

 

View solution in original post

2 REPLIES 2
sturlaws
Resident Rockstar
Resident Rockstar

Hi @OscarSuarez10 

 

Let us take a look at what happens when you look at Hoja[Date]=1, with special attention to this part:

Hoja[Date] <= Earlier(Hoja[Date])

When you look at a row where [Date]=1, this code says find all rows where [Date] is smaller or equal to 1. Hence, from you example, it sums 5, 32 and 25 = 62.

So how to fix this? You are relying on the sorted order of your table. Since you are looking at UniquePageViews, perhaps you have a uniquePageID available? If you have, you could change your code to this:

Running Total Sume =
CALCULATE (
    SUM ( Hoja3[UniquePageViews] );
    FILTER (
        ALLSELECTED ( Hoja3 );
        Hoja3[date] <= EARLIER ( Hoja3[Date] )
            && Hoja3[UniquePageID] = EARLIER ( [UniquePageID] )
    )
)

I have rewritten you code using the ALLSELECTED-function, although ALLSELECTED is a particular difficult function to master. I can't see it has any uses for you here, and unless you have found that it has a purpose in your code, I would recomend that you change it to use the ALL-function.

Now, if don't have a something like a uniquePageID available, we need to rely on the order of Date in the input, and generate a uniqe ID on our own. There are a few steps to this, so instead of describing them, I have created a demo report

Go to Power Query/Edit queries to see how this is done.

 

Cheers,
sturla

 

If this post helps, then please consider Accepting it as the solution. Kudos is also nice.

 

Thank You @sturlaws  It solved the problem!

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.

Top Solution Authors