Reply
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Calculate Total of a Field while Ignoring X Axis
03-19-2021
08:53 AM
Hi,
I am currently trying to calculate the Running Total of Customer Contract Renewals in terms of percentage of total Contracts, and how that trends by Date. Currently, I have figured out a calculation that shows volume of renewals by date ([Days Until Expiry]):
Renewed RCE running total in Days Until Expiry =
CALCULATE(
SUM('Residential - US South'[Renewed RCE]),
FILTER(
ALLSELECTED('Residential - US South'[Days Until Expiry]),
ISONORAFTER('Residential - US South'[Days Until Expiry], MAX('Residential - US South'[Days Until Expiry]), DESC)
)
)
However, I want to view these not in raw number totals but in percentage totals (and compare customer cohorts as the legend). Logically, I get how to calculate this: just divide the running total by the grand total by cohort which should show how that percentage gets closer to 100% over time.
I'm running into an issue, however where that "Grand Total" I try and calculate gets affected by the X axis so instead of showing the grand total for every day in March (for example), on March 1st it shows the grand total for March 1st, March 2nd for March 2nd, etc.
So how do I create this calculation which displays the same value for every day in a month, regardless of the X axis?
Thanks!
Solved! Go to Solution.
1 ACCEPTED SOLUTION
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
03-19-2021
09:30 AM

@NewAnalyst16 , Not very clear.
% can be
Renewed RCE running total in Days Until Expiry %=
divide( CALCULATE(
SUM('Residential - US South'[Renewed RCE]),
FILTER(
ALLSELECTED('Residential - US South'[Days Until Expiry]),
ISONORAFTER('Residential - US South'[Days Until Expiry], MAX('Residential - US South'[Days Until Expiry]), DESC)
)
),CALCULATE(
SUM('Residential - US South'[Renewed RCE]),
ALLSELECTED('Residential - US South'[Days Until Expiry])))
SUM('Residential - US South'[Renewed RCE]),
ALLSELECTED('Residential - US South'[Days Until Expiry])))
Can you share sample data and sample output in table format?
2 REPLIES 2
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
03-19-2021
09:30 AM

@NewAnalyst16 , Not very clear.
% can be
Renewed RCE running total in Days Until Expiry %=
divide( CALCULATE(
SUM('Residential - US South'[Renewed RCE]),
FILTER(
ALLSELECTED('Residential - US South'[Days Until Expiry]),
ISONORAFTER('Residential - US South'[Days Until Expiry], MAX('Residential - US South'[Days Until Expiry]), DESC)
)
),CALCULATE(
SUM('Residential - US South'[Renewed RCE]),
ALLSELECTED('Residential - US South'[Days Until Expiry])))
SUM('Residential - US South'[Renewed RCE]),
ALLSELECTED('Residential - US South'[Days Until Expiry])))
Can you share sample data and sample output in table format?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
03-19-2021
09:42 AM

Incredible Amit, thanks a ton!

Helpful resources
Announcements
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Power BI Monthly Update - February 2025
Check out the February 2025 Power BI update to learn about new features.

Recommendations
Subject | Author | Posted | |
---|---|---|---|
08-29-2024 12:51 PM | |||
09-12-2024 07:08 PM | |||
06-29-2024 06:36 AM | |||
Anonymous
| 07-27-2022 01:20 AM | ||
10-02-2019 12:23 PM |
Featured Topics
Top Solution Authors (Last Month)
User | Count |
---|---|
87 | |
81 | |
53 | |
38 | |
35 |