Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello I want to calculate the cumulative sum of UniquePageViews, to get this results:
Date | UniquePageViews | Running Total Sume |
1 | 5 | 5 |
2 | 25 | 30 |
3 | 70 | 100 |
4 | 8 | 108 |
1 | 32 | 32 |
2 | 1 | 33 |
3 | 25 | 58 |
4 | 36 | 94 |
1 | 25 | 68 |
2 | 43 | 65 |
3 | 22 | 87 |
4 | 10 | 97 |
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:
I dont know why it doesnit work for repeated dates,
Can you help me?
Solved! Go to Solution.
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
21 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |