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

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

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 YearCustRegionRevenueRev_PYYoYChurn
2015.16MMMNA         141,892   228,235   (86,343)     (207,556)

 

Plan YearCustRegionOrg StructureRevenueRev_PYYoYChurn
2015.16MMMNATeam1             1,444              1,444                       -  
2015.16MMMNATeam2                 219                219                       -  
2015.16MMMNATeam3                 188                188                       -  
2015.16MMMNATeam4         133,639         155,998       (22,359)       (195,614)
2015.16MMMNATeam5                  (75)                   75 
2015.16MMMNATeam6             3,201           17,968       (14,767)             (5,971)
2015.16MMMNATeam7            10,722       (10,722) 
2015.16MMMNATeam8                      -                       -   
2015.16MMMNATeam9                      -                       -   
2015.16MMMNATeam10                    82               (82) 
2015.16MMMNATeam11              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's avatar
    jahida
    Icon for Impactful Individual rankImpactful Individual
    10 years ago

    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?

  • 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!