Reply
NewAnalyst16
Frequent Visitor
Partially syndicated - Outbound

Calculate Total of a Field while Ignoring X Axis

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!
1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

Syndicated - Outbound

@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])))
 
Can you share sample data and sample output in table format?
 
Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

Syndicated - Outbound

@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])))
 
Can you share sample data and sample output in table format?
 
Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Syndicated - Outbound

Incredible Amit, thanks a ton!

avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

FebPBI_Carousel

Power BI Monthly Update - February 2025

Check out the February 2025 Power BI update to learn about new features.

Feb2025 NL Carousel

Fabric Community Update - February 2025

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

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)