Forum Discussion

hwan's avatar
hwan
Frequent Visitor
10 years ago
Solved

Churn calculation does not work for total

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)   Th...
Need Help
  • jahida's avatar
    jahida
    10 years ago

    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.

jahida's avatar
jahida
Icon for Impactful Individual rankImpactful Individual
10 years ago

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's avatar
hwan
Frequent Visitor
10 years ago

Thank you jahida. This is very helpful!