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.
Thank you very much jahida! The formula works but it is quite slow as I have 3m lines in my table. Any way to make it faster?
Impactful IndividualI'm not great with PowerBI efficiency, I've heard the Calculate function is implemented quite efficiently though, so you could try something like:
CALCULATE(SUM('Table'[Revenue]), [Revenue]-[Rev_PY]<0) - CALCULATE(SUM('Table'[Revenue]), [Revenue]-[Rev_PY]<0)
Obviously the above could be compressed using a similar SUMX as above but I would guess that SUM is implemented much more efficiently. Not having a 3m row dataset to test on, I can't conclusively say what would be fast though.
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
- jahida
To clarify, are the numbers there current result or expected/desired result? And can I get the other one as well to understand the problem better?
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
- jahida
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.