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

     

     

     

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!