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.
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!
Solved! Go to Solution.
@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
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
@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
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
@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.
I am unable to provde a sample, are you able to help with just the information I've provided?
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 !!!
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!
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 |
---|---|
113 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |