cancel
Showing results for
Did you mean:
New Member

## How to calculate a correct cumulative percentage

Hi,

In my raw data (group by Year, Vintage and Age), I have two columns called 'total cash' and 'total purchase balance' and I have a measure called 'total payment %' = sum(total cash)/sum(total purchase balance). Below is an example to show the "total payment %"

 Orig Year Vintage age0 age1 age2 age3 2023 Jan, 2023 5.85% 18.21% 14.12% 11.22% Feb, 2023 5.78% 19.65% 13.27% Mar, 2023 6.76% 17.05%

for the next step, I want to calcualte cumulative total payment % so that the results look like below table

 Orig Year Vintage 0 1 2 3 2023 Jan, 2023 5.85% 5.85%+18.21%=24.06% 24.06%+14.12%=38.18% 38.18%+11.22%=49.4% Feb, 2023 5.78% 5.78%+19.65%=25.43% 25.43%+13.27%=38.7% Mar, 2023 6.76% 6.76%+17.05%=23.81%

However, when I use quick measure 'running total' to calculate 'total payment%' over age, I got wrong number:

It seems like the measure calculates number based on running sum 'total cash' and sum 'total purchase balance' first and then make the division rather than calculate percentage first and then running sum the percentage:

for example:

if total payment % (Age1) = 10/100=10%, total payment % (age2) = 12/120=10%

my expected cumulative total payment % for age 2 shoud be 10%+10% = 20%

but Power BI return me (10+12)/(100+120)=10%.

Any Idea will be really helpful! thanks a lot

1 ACCEPTED SOLUTION
Super User

@zhengho , Assume you already have a measure total payment %

Cumm Sales = CALCULATE(Sumx(Values('Date'[Month Year]), [total payment %]),filter(all('Date'),'Date'[date] <=max('Date'[date])))

or

Cumm = CALCULATE(Sumx(values('Date'[Month Year]),[total payment %]) ,filter(allselected('Date'),'Date'[date] <=max('Date'[date])))

2 REPLIES 2
Super User

@zhengho , Assume you already have a measure total payment %

Cumm Sales = CALCULATE(Sumx(Values('Date'[Month Year]), [total payment %]),filter(all('Date'),'Date'[date] <=max('Date'[date])))

or

Cumm = CALCULATE(Sumx(values('Date'[Month Year]),[total payment %]) ,filter(allselected('Date'),'Date'[date] <=max('Date'[date])))

New Member

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors