cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
angel1 Frequent Visitor
Frequent Visitor

Don't want monthly balances to sum in pivot table because they are already cumulative

I have a pivot table with balances for each month.  The issue is that my balances are already cumulative, so when I drill up to the Year, quarter, it's summing the balances and I don't want them to sum. I only want to show the balances for each month and when I drill up to the full year or latest quarter, it should only show the most recent month's balances because as I mentioned they are already cumulative each  month.  Any help would be greatly appreciated!

1 ACCEPTED SOLUTION

Accepted Solutions
angel1 Frequent Visitor
Frequent Visitor

Re: Don't want monthly balances to sum in pivot table because they are already cumulative

@TomMartens Tom- Thanks so much for the prompt response and sharing the article! I'm sure it contains the solution I need, but I'm not skilled enough to know how to apply the DAX to my specific situation.  I simply have the month as the column headers with drill downs for Quarter and Year and I have the Balances in the rows.  So for example: My balances in March were 1,000,000, my balances in April were 2,000,000 and my balances in May were 3,000,000.  When I drill to the Quarter and Year, I want my balances to show 3,000,000, summarized for the most recent month only, but instead it's summing the balances across all of the months and returning 6,000,000.  Also, If I filter for a previous month, I would need it to summarize YTD, QTD, etc. for whatever month I've filtered on.  Are you able to provide me with more specific code than the article provides?  I am just not sure how to tweak their code to my unique situation. Thank you so much in advance!


@TomMartens wrote:

Hey,

 

the challenge you are facing has a name (know your enemy :-) ), it's called semi-additive measure.

This article describes almost anything that will surface over time tha's time related: https://www.daxpatterns.com/time-patterns/

Watch for the semi-additve pattern.

 

Hopefully this provides the insights you are looking for.

 

Regards,

Tom




7 REPLIES 7
Super User
Super User

Re: Don't want monthly balances to sum in pivot table because they are already cumulative

Hey,

 

the challenge you are facing has a name (know your enemy :-) ), it's called semi-additive measure.

This article describes almost anything that will surface over time tha's time related: https://www.daxpatterns.com/time-patterns/

Watch for the semi-additve pattern.

 

Hopefully this provides the insights you are looking for.

 

Regards,

Tom

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
angel1 Frequent Visitor
Frequent Visitor

Re: Don't want monthly balances to sum in pivot table because they are already cumulative

Tom- Thanks so much for the prompt response and sharing the article! I'm sure it contains the solution I need, but I'm not skilled enough to know how to apply the DAX to my specific situation.  I simply have the month as the column headers with drill downs for Quarter and Year and I have the Balances in the rows.  So for example: My balances in March were 1,000,000, my balances in April were 2,000,000 and my balances in May were 3,000,000.  When I drill to the Quarter and Year, I want my balances to show 3,000,000, summarized for the most recent month only, but instead it's summing the balances across all of the months and returning 6,000,000.  Also, If I filter for a previous month, I would need it to summarize YTD, QTD, etc. for whatever month I've filtered on.  Are you able to provide me with more specific code than the article provides?  I am just not sure how to tweak their code to my unique situation. Thank you so much in advance!

angel1 Frequent Visitor
Frequent Visitor

Re: Don't want monthly balances to sum in pivot table because they are already cumulative

@TomMartens Tom- Thanks so much for the prompt response and sharing the article! I'm sure it contains the solution I need, but I'm not skilled enough to know how to apply the DAX to my specific situation.  I simply have the month as the column headers with drill downs for Quarter and Year and I have the Balances in the rows.  So for example: My balances in March were 1,000,000, my balances in April were 2,000,000 and my balances in May were 3,000,000.  When I drill to the Quarter and Year, I want my balances to show 3,000,000, summarized for the most recent month only, but instead it's summing the balances across all of the months and returning 6,000,000.  Also, If I filter for a previous month, I would need it to summarize YTD, QTD, etc. for whatever month I've filtered on.  Are you able to provide me with more specific code than the article provides?  I am just not sure how to tweak their code to my unique situation. Thank you so much in advance!


@TomMartens wrote:

Hey,

 

the challenge you are facing has a name (know your enemy :-) ), it's called semi-additive measure.

This article describes almost anything that will surface over time tha's time related: https://www.daxpatterns.com/time-patterns/

Watch for the semi-additve pattern.

 

Hopefully this provides the insights you are looking for.

 

Regards,

Tom




Super User
Super User

Re: Don't want monthly balances to sum in pivot table because they are already cumulative

Hey Angel,

 

please provide a pbix file that contains sample data but represents your data model (including existing measures and calculated columns), upload the pbix file to onedrive or dropbox and share the link. If you use an Excel file for the sample data, then also upload the xlsx and share the link also.

 

Regards,

Tom

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
angel1 Frequent Visitor
Frequent Visitor

Re: Don't want monthly balances to sum in pivot table because they are already cumulative

@TomMartens  Thanks for the offer, unfortunately, I don't have access to one drive or drop box, thought I might be able to load a file here.

angel1 Frequent Visitor
Frequent Visitor

Re: Don't want monthly balances to sum in pivot table because they are already cumulative

I am unable to provde a sample, are you able to help with just the information I've provided?

angel1 Frequent Visitor
Frequent Visitor

Re: Don't want monthly balances to sum in pivot table because they are already cumulative

Just tried the semi-additive measure on my own and it worked the 1st time like a charm! How many times does that that happen? Thank you !!!