Forum Discussion
Cumulative percentage (CALCULATE FUNCTION)
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.
Resolver IITry 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.
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 :
| 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
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.