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.

hwan's avatar
hwan
Frequent Visitor
10 years ago

Sorry for the confusion. Below 2 charts are the current results.

 

1. At team level

Plan YearCustOrg StructureRevenueRev_PYYoYChurn
2015.16MMMTeam1                           1,444              1,444                       -  
2015.16MMMTeam2                               219                219                       -  
2015.16MMMTeam3                               188                188                       -  
2015.16MMMTeam4                       133,639         155,998       (22,359)       (195,614)
2015.16MMMTeam5                  (75)                   75 
2015.16MMMTeam6                           3,201           17,968       (14,767)             (5,971)
  Total                       138,691         173,891       (35,200)       (201,585)

 

2. at region level

 

Plan YearCustRevenueRev_PYYoYChurn
2015.16MMM           138,691         173,891       (35,200)       (201,585)

 

The team level is good. I would like to have the regional level looks like -

 

Plan YearCustRevenueRev_PYYoYChurn
2015.16MMM           138,691         173,891       (35,200)

         (35,200)

 

Thanks,

Hui

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!