Cumulative percentage (CALCULATE FUNCTION)

[readyOH]

Hi,

For cumulative data, I use CALCULATE SUM AND FILTER functions. it works pretty well

For example, if i want to have the cumulative sales

SalesCumul = CALCULATE(SUM('Table1'[Sales]);FILTER(ALL('Table1'[Month]);'Table1'[Month]<= MAX('Table1'[Month])))

but when I want to cumulate percentage it does not work.

For example, I have a measure in percent :  SavingRate = sum(saving)/sum(purchase)

When i want to cumulate it. So

January : JanuarySavingRate

February : JanuarySavingRate + FebruarySavingRate

March : FebruarySavingRate + MarchSavingRate

etc...

it does not work

my formula is

CumulSavingRate = CALCULATE(SUM('Table1'[SavingRate]);FILTER(ALL('Table1'[Month]);'Table1'[Month]<= MAX('Table1'[Month])))

I can't find the solution

thanks

[ALeef]

Try using SUMX to iterate through all the rows.  Also, while you can SUM the values, be careful what you are actually asking for.  By adding percentages, you will end up with a mostly meaningless number.  I saved 6% this month, 8% last month, and 14% the month before.  Overall did I save 28%?  Should I be averaging them instead?

I think what you are actually looking for is a total for the whole table of savings / purchase.  I would calculate those both cumulatively, like you did with sales - and then divide the results of those two to get cumulative SavingsRate.  

So you would have SavingsCumul and PurchaseCumul , and a third measure names SavingRateCumul which is SavingsCumul / PurchaseCumul.

Make sense?  A bit of data structure would help to visualize it.  Or you can use calculated columns at lowest granularity instead of measures to make the calcs more static.

[readyOH]

@ALeef thank you.

ALeef wrote: I saved 6% this month, 8% last month, and 14% the month before.  Overall did I save 28%?  Should I be averaging them instead?

no because this will be a cumulativeSaving/TotalPurchase

A sample data &colon;

A	B	C	D	E	F
	Saving	Purchase	SavingCumul	Monthly Saving % of purchase B/C	SavingCumul in % B/728
January	2	101	2	1,98%	0,27%
February	3	102	5	2,94%	0,69%
March	4	103	9	3,88%	1,24%
April	5	104	14	4,81%	1,92%
May	6	105	20	5,71%	2,75%
June	7	106	27	6,60%	3,71%
July	8	107	35	7,48%	4,81%
Total	35	728

For example, if we have a target 7% of saving at the end of the year, thanks to my measure SavingCumul in %, I will see the gap between us and our target (my target is also a cumulative measure of my monthly targets).if we don't make cumulative, we only track month by month without knowing if we are in good way to reach the target

So we do this type of figures

@ALeef wrote:

I think what you are actually looking for is a total for the whole table of savings / purchase.  I would calculate those both cumulatively, like you did with sales - and then divide the results of those two to get cumulative SavingsRate.  

So you would have SavingsCumul and PurchaseCumul , and a third measure names SavingRateCumul which is SavingsCumul / PurchaseCumul.

Sounds great but I tried and it does not work because I have to make SavingsCumul / Total Purchase

So February for example it has to be (Saving Jan + Saving Feb) / (Total Purchase Jan to July)

and not   (Jan + Saving Feb) / (Purchase Jan + Purchase Feb)

[ALeef · Accepted solution]

Ahh, so you are looking for attainment of goal.  That makes more sense now.

So currently, you have Column E (from your example) working - and Column F is what you are trying to solve?

In that case, if you need to keep static numbers for each month ( i.e. in February we had saved 5, and at that time, were at .69% of our 7% goal ) you need to use a calculated column for the SavingsCumul, not a measure.

Your columns will be as follows : (using pseudo-code, not actual syntax - new/changed columns bolded )

Savings: <fact data>

Purchase: <fact data>

SavingsCumul: calculate sum of [Savings], filtered by [month], where [month] <= [month]

MonthlySavings: [savings] / [purchase] (and then format the column as a percent)

Attainment of goal to date : [savingsCumul] / [PurchasesYTD] (format as percentage)

Then you can add the measure : PurchasesYTD: = calculate sum of [purchases]

Then you will be able to see your percentage of savings for each month, as compared to total purchases.  Keep in mind that those percentages in the Attainment column will change when you refresh your data

Below is a theoretical discussion of analytics, and not at all related to answering your question.  Feel free to ignore it if you wish.:

Keep in mind that those percentages in the Attainment column will change when you refresh your data ... so realistically, the only number that makes a difference in terms of tracking your KPIs, will be the last month anyways.  Not trying to be a jerk - as I don't understand your particular situation - just, as a Business Analyst, knowing how much we had saved in February as a percentage of what we sold YTD, is a useless metric.  I would be interested in knowing "how much have we saved YTD compared to purchased YTD?" - which is what your first set of measures was calculating fine.  If you need to track it over time, then it makes sense to track Cumulative Savings vs Cumulative Purchases - to find out your percentage to goal as of that month.  But doing it vs YTD doesn't make sense.