Forum Discussion

tomislav_mi's avatar
tomislav_mi
Icon for Helper II rankHelper II
5 years ago
Solved

Dynamic Agregation - hardest so far

Hey guys,

I am struggling with one of the hardest mind games I have encountered in DAX so far.

I have a data set that can be pivoted to cohort table like this:

 Sum of EndingMRR  YearsActive     
 Row Labels                                 0             1           2             3             4           5
 2020-01                        8,000     7,000     7,000     6,800     7,800   7,500
 2020-02                      16,000   15,000   13,000   12,000   12,000 
 2020-03                        5,000     4,300     4,000     4,000  
 2020-04                        7,000     7,500     7,500   
 2020-05                      14,000   14,100    
 Grand Total                      50,000   47,900   31,500   22,800   19,800   7,500


I would need to divide MRR from each active year with their base from zero year - i.e. I would need to get retention % of all those customers that are active for a certain number of years.

(for example numbers in orange: for 4th year that would be 19,800 divided with only the sum of orange numbers
from zero years that is 24,000 and I would get 82.5%;
for 5th year that would be 7,500 divided by only 8,000 from zero year etc)

I have tried to do it with the following formula

=
DIVIDE (
    SUM ( CustomerData[EndingMRR] ),
    CALCULATE (
        SUM ( CustomerData[EndingMRR] ),
         ( CustomerData[YearsActive] = "0" )
    )
)


but this is wrong because it is taking the sum of all the numbers from zero year instead of only specific ones.

Using excel to show you the logic and the outcome:

 
 


Please help! Any advice is welcome.

I appreciate your help guys!

Thank you!

Aggregation
CALCULATE
DAX
Filter
Help me please
Measures
  • CNENFRNL's avatar
    CNENFRNL
    5 years ago

    Hi, tomislav_mi , such a cohort table of dataset does please the eye; but troubles DAX. As for me, I always manage datasets to one-dimensional table whenever possible.

    So, unpivot the beautiful cohort table into a plain one like this

    then try these measures

     

     

     

     

    SumUp = SUM( CustomerData[EndingMRR] )

% Retention = 
DIVIDE (
    [SumUp],
    CALCULATE ( [SumUp], CustomerData[YeasActive] = "0", VALUES ( CustomerData[RowLabels] ) )
)

     

     

     

3 Replies

  • CNENFRNL's avatar
    CNENFRNL
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Hi, tomislav_mi , such a cohort table of dataset does please the eye; but troubles DAX. As for me, I always manage datasets to one-dimensional table whenever possible.

    So, unpivot the beautiful cohort table into a plain one like this

    then try these measures

     

     

     

     

    SumUp = SUM( CustomerData[EndingMRR] )

% Retention = 
DIVIDE (
    [SumUp],
    CALCULATE ( [SumUp], CustomerData[YeasActive] = "0", VALUES ( CustomerData[RowLabels] ) )
)

     

     

     

    • tomislav_mi's avatar
      tomislav_mi
      Icon for Helper II rankHelper II
      5 years ago

      CNENFRNL 

      Actually already had it in unpivoted format - VALUES was the key function.

      It PERFECTLY works!

      Thank you friend!

      All the best!

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    tomislav_mi , Try

    =
    DIVIDE (
    SUM ( CustomerData[EndingMRR] ),
    CALCULATE (
    SUM ( CustomerData[EndingMRR] ),
    filter(all(CustomerData), CustomerData[YearsActive] = "0" )
    )
    )

     

    or

     

    =
    DIVIDE (
    SUM ( CustomerData[EndingMRR] ),
    CALCULATE (
    SUM ( CustomerData[EndingMRR] ),
    filter(allselected(CustomerData), CustomerData[YearsActive] = "0" )
    )
    )