Forum Discussion
Churn calculation does not work for total
Hey, no worries. I don't know of a way to make the same measure work for both use cases; maybe it's possible but certainly not trivial. Here's an adapted formula (I'd imagine it will be a fair amount slower though) to use when you're looking at the regional level:
Churn = SUMX(SUMMARIZE('Table', 'Table'[Cust], 'Table'[Plan Year], "Revenue1", SUM([Revenue]), "Rev_PY1", SUM([Rev_PY])), if(([Revenue1]-[Rev_PY1])<0,[Revenue1]-[Rev_PY1],0))
Not testing so there may be minor issues, and I may have grouped by the wrong columns for your purposes, but in general the SUMMARIZE function is definitely your friend here - it will allow you to specify the level of aggregation before doing the calculation.
I took another look at the calculation with the sumx formula and have a question -
I have another table for region/market.With the SUMX formula the Churn is calculated at the lowest hierachy level. So if I look at the region level for a particular client, the churn does not necessarily equal to the regional revenue decline over year.
For example, if you look at customer MMM for region NA, the Churn number looks off as it is based on calculation on team level.
| Plan Year | Cust | Region | Revenue | Rev_PY | YoY | Churn |
| 2015.16 | MMM | NA | 141,892 | 228,235 | (86,343) | (207,556) |
| Plan Year | Cust | Region | Org Structure | Revenue | Rev_PY | YoY | Churn |
| 2015.16 | MMM | NA | Team1 | 1,444 | 1,444 | - | |
| 2015.16 | MMM | NA | Team2 | 219 | 219 | - | |
| 2015.16 | MMM | NA | Team3 | 188 | 188 | - | |
| 2015.16 | MMM | NA | Team4 | 133,639 | 155,998 | (22,359) | (195,614) |
| 2015.16 | MMM | NA | Team5 | (75) | 75 | ||
| 2015.16 | MMM | NA | Team6 | 3,201 | 17,968 | (14,767) | (5,971) |
| 2015.16 | MMM | NA | Team7 | 10,722 | (10,722) | ||
| 2015.16 | MMM | NA | Team8 | - | - | ||
| 2015.16 | MMM | NA | Team9 | - | - | ||
| 2015.16 | MMM | NA | Team10 | 82 | (82) | ||
| 2015.16 | MMM | NA | Team11 | 7,384 | (7,384) | ||
| Total | 138,691 | 192,079 | (53,388) | (201,585) |
Is there a way to calculate this at the hierachy level as the one indicated in the chart/visualization?
Thanks,
Hui
Sorry for the confusion. Below 2 charts are the current results.
1. At team level
| Plan Year | Cust | Org Structure | Revenue | Rev_PY | YoY | Churn |
| 2015.16 | MMM | Team1 | 1,444 | 1,444 | - | |
| 2015.16 | MMM | Team2 | 219 | 219 | - | |
| 2015.16 | MMM | Team3 | 188 | 188 | - | |
| 2015.16 | MMM | Team4 | 133,639 | 155,998 | (22,359) | (195,614) |
| 2015.16 | MMM | Team5 | (75) | 75 | ||
| 2015.16 | MMM | Team6 | 3,201 | 17,968 | (14,767) | (5,971) |
| Total | 138,691 | 173,891 | (35,200) | (201,585) |
2. at region level
| Plan Year | Cust | Revenue | Rev_PY | YoY | Churn |
| 2015.16 | MMM | 138,691 | 173,891 | (35,200) | (201,585) |
The team level is good. I would like to have the regional level looks like -
| Plan Year | Cust | Revenue | Rev_PY | YoY | Churn |
| 2015.16 | MMM | 138,691 | 173,891 | (35,200) | (35,200) |
Thanks,
Hui
jahidaImpactful Individual
Hey, no worries. I don't know of a way to make the same measure work for both use cases; maybe it's possible but certainly not trivial. Here's an adapted formula (I'd imagine it will be a fair amount slower though) to use when you're looking at the regional level:
Churn = SUMX(SUMMARIZE('Table', 'Table'[Cust], 'Table'[Plan Year], "Revenue1", SUM([Revenue]), "Rev_PY1", SUM([Rev_PY])), if(([Revenue1]-[Rev_PY1])<0,[Revenue1]-[Rev_PY1],0))
Not testing so there may be minor issues, and I may have grouped by the wrong columns for your purposes, but in general the SUMMARIZE function is definitely your friend here - it will allow you to specify the level of aggregation before doing the calculation.
Thank you jahida. This is very helpful!