- Forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Integrations with Files and Services
- Mobile Apps
- Developer
- Let's Talk Data
- Custom Visuals Development Discussion
- Community Support
- Welcome to the Community
- Community Feedback
- Community Help Blog
- Training and Consulting
- Dashboard in a Day
- EdX Specific Training Discussion Forum

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Re: Cumulative sum with VALUES filter issue?

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Chamara

Frequent Visitor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-04-2017
03:53 PM

I want to show the Cumulative sum of positive numbers. I also want to have another Cumulative sum that adjusts with some slicers. I created 2 measures to do this. Cumulative Sum works fine. Hower my second Measure does not show expected results (basically cumulative sum cannot reduce from one year to the next for positive numbers right ). Please see below

Following are the 2 Measures I used,

**Cumulative Rev = ****CALCULATE (**** SUM ( Data1[Revenue] ), **** FILTER (**** ALL ( Data1 ),**** Data1[TargetDate] <= MAX ( Data1[TargetDate]))****)**

Following measure is the one that dip down unexpectadly. Note, the only difference is the additional filter on (Data1.Classification). This additional Filter helps the Cumlative Rev Filter to respond to the slicer (also shown above).

Cumulative Rev Filtered =

CALCULATE (

SUM ( Data1[Revenue] ),

FILTER (

ALL ( Data1 ),

Data1[TargetDate] <= MAX ( Data1[TargetDate])),

VALUES(Data1[Classification])

)

I am attaching the source data excel file and pbix showing this issue.

My questions.

1. How can I get a cumulative sum filter based on a slicer if the above is not the intended way?

2. Is the above a bug?

Solved! Go to Solution.

1 ACCEPTED SOLUTION

Accepted Solutions

Highlighted

v-yuezhe-msft

Moderator

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-10-2017
03:07 AM

Hi @Chamara,

Please use the following formula to create Cumulative Sum Filtered measure and check if you get expected result.

Cumulative Sum Filtered = CALCULATE (

SUM ( Data1[Revenue] ),

FILTER(ALLEXCEPT(Data1,Data1[Classification]), Data1[TargetDate] <= MAX ( Data1[TargetDate])

)

)

Thanks,

Lydia Zhang

Community Support Team _ Lydia Zhang

If this post**helps**, then please consider *Accept it as the solution* to help the other members find it more quickly.

If this post

6 REPLIES 6

dkay84_PowerBI

New Contributor

Re: Cumulative sum with VALUES filter issue?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-04-2017
04:17 PM

I will take a look at helping you with your filtered cumulative sum measure, but PLEASE tell me how you were able to attach a file to your post? I've always had to add a link to a shared drive for files.

Chamara

Frequent Visitor

Re: Cumulative sum with VALUES filter issue?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-04-2017
04:35 PM

@dkay84_PowerBI,Re: Attaching files: It works on FireFox and not IE. In Firefox I get an option at the bottom to attach files even for replies.

v-yuezhe-msft

Moderator

Re: Cumulative sum with VALUES filter issue?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-09-2017
02:52 AM

Hi @Chamara,

I check the DAX formulas you use in the PBIX file and the Line Chart, there are no issues, when selecting different value in Classifications slicer, Cumulative Rev Filtered measure returns correct value. Which result do you want to get?

In your screenshot, as there is no revenue for Consumer at 2020, the Cumulative Rev Filtered measure returns a smaller value than that at 2019.

Thanks,

Lydia Zhang

Community Support Team _ Lydia Zhang

If this post**helps**, then please consider *Accept it as the solution* to help the other members find it more quickly.

If this post

Chamara

Frequent Visitor

Re: Cumulative sum with VALUES filter issue?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-09-2017
01:57 PM

@v-yuezhe-msft, Thank you for the reply. My main issue is when no value is selected on the slicer, I expect the "Cumulative sum Filtered" to match with "Cumulative Sum" for all years (the two colored columns show this). Following are my expected values.

For example, 2022 "Cumulative sum" is derived as by summing up "Sum of Revenue" for all years up until 2022 inclusive = $199,568,880 + $90,584,908 + $20,245,022 + $1,228,799 + $320,305,949 = $511,196,490. This value for 2022 matches for both "Cumulative Sum" and "Cumulative Sum Filtered" somehow in the Power BI report I attached. Also "Cumulative Sum" calculation has expected values for each year which is the "Sum of Revenue" for current year + "Cumulative Sum" for previous year in the same report. Question is, why don't I see the same values as the above table for "Cumulative Sum Filtered" in Power BI report when nothing is selected on the slicer.

The general formula for

"cumulative sum for current year" = "cumulative sum for previous year" + "sum for current year"

With this, if sum for current year is 0, then "cumulative sum for current year" should match "cumulative sum for previous year" (i.e. "cumulative sum for current year" cannot go below what was the "cumulative sum for previous year" ). The only way cumulative sum from one year to another can go down is if the "sum for current year" is negative.

With that, I do not get your logic to getting a smaller value for 2020. Even if that logic is true, then it does not explain how the "Cumulative Sum Filtered" catches up with "Cumulative Sum" in 2022 after dipping in 2020 and 2021.

Thank you,

Chamara

Highlighted

v-yuezhe-msft

Moderator

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-10-2017
03:07 AM

Hi @Chamara,

Please use the following formula to create Cumulative Sum Filtered measure and check if you get expected result.

Cumulative Sum Filtered = CALCULATE (

SUM ( Data1[Revenue] ),

FILTER(ALLEXCEPT(Data1,Data1[Classification]), Data1[TargetDate] <= MAX ( Data1[TargetDate])

)

)

Thanks,

Lydia Zhang

Community Support Team _ Lydia Zhang

If this post**helps**, then please consider *Accept it as the solution* to help the other members find it more quickly.

If this post

Chamara

Frequent Visitor

Re: Cumulative sum with VALUES filter issue?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-11-2017
11:00 AM

@v-yuezhe-msftYour DAX actually give the data I need and I marked it as the answer. Thank you for that. However, I believe the formula I had initially for 'Cumulative Sum Filtered' should give the same results and that it is a bug. I do not discount the third possibility that I have not understood the DAX used in that. Either way, I would like to get closure on that as well and will log a bug in couple of days with that intention.