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
gutierres
Frequent Visitor

Combining filters and cumulative sum

I need to show the cumulative value of a sum of some columns, but I need to merge the cumulative sum with some restrictions aswell.

I have some filters that can be applied (like City, Department, id, and time period). For example, this code works:

Cumulative_sum = CALCULATE( SUM(Table[ColumnA]) + SUM(Table[ColumnB]) + SUM(Table[ColumnC]); FILTER(ALL(Table(TIME_PERIOD); Table[TIME_PERIOD] <= MAX (Table[TIME_PERIOD])))

When I tried to add the filter, my coding either returns an error or returns the cumulative sum starting on the time period filtered: For example, let's say I have a value of 10 in January, Feb, March, and April for the city A, department B. If I choose to show the values of March and April, it should show 30 and 40. However, the current graph shows the cumulative value of the filtered time period (and would show 10 and 20).

Here is what Im trying:

   Cumulative_sum = CALCULATE( SUM(TableA[ColumnA]) + SUM(TableA[ColumnB]) + SUM(TableA[ColumnC]); FILTER(ALL(TableB(TIME_PERIOD); TableB[TIME_PERIOD] <= MAX (TableB[TIME_PERIOD])); FILTER(ALLSELECTED(TableA); TableA

[SITUATION] = "OK'))

I have tried using the a column of time period inside TableA, and tried using TableB as a Time Period table independently. What is my mistake here?

5 REPLIES 5
Ashish_Mathur
Super User
Super User

Hi,

 

Share a dataset and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

How can I attach a file?

Edit: I uploaded the file at https://ufile.io/gdalp if that's ok

You can see in the graph, when showing the last 5 months (calendar), that the value for the feb 26 is 64. When I change the filter to show the last 4 months (calendar), it gives back 10 for feb 26, and thats the problem: When I add the filter to check if situation = "OK", the cumulative filter stop working as intended. ( It should show 64)

Hi,

 

Upload the file to Google Drive and share the download link here.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi,

 

Your question is not clear.  Convert the line graph into a simple Table.  Now tell e what exact numbers are you expecting against every date in the Time period column.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

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

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.