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   Ye...
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] ) )
)

     

     

     

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