Churn calculation does not work for total

[hwan]

Hello,

I am trying to calculate revenue churn which is defied as revenue decrease year over year at client level. I have a measure 

Churn = if(([Revenue]-[Rev_PY])<0,[Revenue]-[Rev_PY],0)

The result works at the client level but not at total level - you can see that the total churn $ is calculated as total revenue of 2015.16 - prior year, which is not what I wanted.

What is the right formula to get it right at both customer and all up levels?

Thanks,

Hui

[jahida]

You need this calculation to occur at the row level, not the aggregated level. Currently, it's just doing the aggregated calculation. Try wrapping in a SUMX as an easy solution:

Churn = SUMX('Table', if(([Revenue]-[Rev_PY])<0,[Revenue]-[Rev_PY],0))

[hwan]

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?

[jahida]

I'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.

[hwan]

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

[hwan]

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 · Accepted solution]

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.

[hwan]

Thank you jahida. This is very helpful!

[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?