Dynamic Agregation - hardest so far

[tomislav_mi]

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!

[amitchandak]

@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" )
)
)

[CNENFRNL · Accepted solution]

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]

@CNENFRNL 

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

It PERFECTLY works!

Thank you friend!

All the best!