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)

 

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

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.

8 Replies

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

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

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