How to create a running total percent table?

[ironryan77]

How can I create a running total percent column so that it always goes up to 100% by the end of the month?  I created this table which has MTD (Month To Date) Posted %, MTD Running Total, and Total Payment Amount, and broken according to day of the month (CRD Day).  I think this is either a DAX problem or else an "Edit Interactions" problem.  This first screenshot is how this table looks with all of my slicers on the default "All" setting, except for CRD_YY which is set for 2015.

Table without any slicer values applied except for the year slicer

As you can see, the last day (31) doesn't equal 100% like I need it to.  But now if leave all of the slicers on "All" except for the month slicer, the final day does equal 100%.  How can I get the final day to always be 100% regardless of what I choose for the other slicers?

Only the CRD_MM has been selected to 4 (April) 

Here were my DAX formulas for these columns:

CRD Day = DAY(Cash_Goals_Cash_Velocity[CRD])

MTD 2015 Posted % = SUMX(SUMMARIZE(Cash_Goals_Cash_Velocity,[CRD Day]),
CALCULATE([MTD Running Total] / [Total Payment Amount],ALLSELECTED(Cash_Goals_Cash_Velocity),Cash_Goals_Cash_Velocity[CRD_YY]=2015))

MTD Running Total = TOTALMTD(SUM([net_payment_amount]),Cash_Goals_Cash_Velocity[CRD],ALL(Cash_Goals_Cash_Velocity[CRD],Cash_Goals_Cash_Velocity[CRD_DDQQ],Cash_Goals_Cash_Velocity[CRD_DDYY], Cash_Goals_Cash_Velocity[CRD Day]))

Total Payment Amount = CALCULATE(SUM(Cash_Goals_Cash_Velocity[net_payment_amount]),ALL(Cash_Goals_Cash_Velocity[CRD],Cash_Goals_Cash_Velocity[CRD_DDQQ],Cash_Goals_Cash_Velocity[CRD_DDYY], Cash_Goals_Cash_Velocity[CRD Day]))

[v-sihou-msft]

@ironryan77

Both tables return incorrect results. In your first table, I think the issue is the "Total Payment Amount", you have limited the context into ALL table, so even you slice on Year 2015, it still calculate the total for all years. For the second chart, the data doesn't make sense. As you can see, some entries have same Running Total, but the percentage is still increasing.

In this scenario, I assume your CRD column is date column, and you also have CRD_YY, CRD_QQ, CRD_MM, CRD_DD columns for different date parts. So if you want to calculate the running total percent monthly, you just need to use MTD divide by monthly total.

The DAX can be like:

MTD Running Total =
TOTALMTD ( SUM ( [net_payment_amount] ), Cash_Goals_Cash_Velocity[CRD] )

MTD Pct =
TOTALMTD ( SUM ( [net_payment_amount] ), Cash_Goals_Cash_Velocity[CRD] )
    / CALCULATE (
        SUM ( [net_payment_amount] ),
        ALLEXCEPT (
            Cash_Goals_Cash_Velocity,
            Cash_Goals_Cash_Velocity[CRD_YY],
            Cash_Goals_Cash_Velocity[CRD_MM]
        )
    )

Regards,

[ironryan77 · Accepted solution]

Thank you for your response!  I really appreciate it!  But how did you calculate the different series based off of these?  These series should all be increasing month-to-month.  Here are my two DAX formulas; the first one is from you.

MTD Running Total test = TOTALMTD(SUM(Cash_Goals_Cash_Velocity[net_payment_amount]),Cash_Goals_Cash_Velocity[CRD])

MTD 2015 Net test = SUMX(SUMMARIZE(Cash_Goals_Cash_Velocity,[CRD Day]),
CALCULATE([MTD Running Total test],Cash_Goals_Cash_Velocity[CRD_YY]=2015))

But here is what did work for me:

MTD Running Total = TOTALMTD(SUM(Cash_Goals_Cash_Velocity[net_payment_amount]),Cash_Goals_Cash_Velocity[CRD],ALL(Cash_Goals_Cash_Velocity[CRD],Cash_Goals_Cash_Velocity[CRD_DDQQ],Cash_Goals_Cash_Velocity[CRD_DDYY], Cash_Goals_Cash_Velocity[CRD Day],Cash_Goals_Cash_Velocity[CRD_YY],Cash_Goals_Cash_Velocity[CRD_QQ]))

MTD 2015 Net = SUMX(SUMMARIZE(Cash_Goals_Cash_Velocity,[CRD Day]),
CALCULATE([MTD Running Total],ALLSELECTED(Cash_Goals_Cash_Velocity),Cash_Goals_Cash_Velocity[CRD_YY]=2015))