Iterative average of growth ratio

[kegoosse]

Hi everyone,

In this file I'm trying to calculate survival rates of cohortes for the Belgian popualtion.

In the visualisation you can find 2 tables with 2 alternative versions for the calculation of the measure. Both are correct for a specific year, but I'm not getting the desired value for the total

For example, for cohort 1_0_44084 this should be 104.86%

i.e. average of :

98.89

98.78

93.81

109.8

135.63

104.1

100

97.84

The first measure (i.e. Average survival rate) isn't getting the desired result because the numerator and denominator aren't being iterated in the AVERAGEX function

Average survival rate = 
VAR HuidigeLeeftijd = MAX('Ruwe data Tellingen StatBel'[Leeftijd])
VAR VorigeLeeftijd = MAX('Ruwe data Tellingen StatBel'[Leeftijd]) - 1
VAR HuidigGeslacht = MAX('Ruwe data Tellingen StatBel'[Geslacht 0/1])
VAR HuidigGemeente = MAX('Ruwe data Tellingen StatBel'[Hoogste Refnis-code van gemeente])
VAR CohorteHuidig = HuidigeLeeftijd&"_"&HuidigGeslacht&"_"&HuidigGemeente
VAR CohorteVorig = VorigeLeeftijd&"_"&HuidigGeslacht&"_"&HuidigGemeente
VAR AantalHuidig = CALCULATE(
    SUM('Ruwe data Tellingen StatBel'[Aantal]),
    'Ruwe data Tellingen StatBel'[Cohorte (leeftijd_geslacht_stad)] = CohorteHuidig
)
VAR AantalVorig = CALCULATE(
    SUM('Ruwe data Tellingen StatBel'[Aantal]),
    'Ruwe data Tellingen StatBel'[Cohorte (leeftijd_geslacht_stad)] = CohorteVorig,
    SAMEPERIODLASTYEAR('Date'[Date])
)
VAR SurvivalRate = AantalHuidig/AantalVorig
RETURN
AVERAGEX( 
    SUMMARIZE(
        'Ruwe data Tellingen StatBel',
        'Date'[Year],
        'Ruwe data Tellingen StatBel'[Cohorte (leeftijd_geslacht_stad)]
    ),
    SurvivalRate
)

However, when I add these parts in the AVERAGEX-function the total returns infinity (for measure Average survival rate 2). I'm not completely sure why this happens.

Average survival rate 2 = 
VAR HuidigeLeeftijd = MAX('Ruwe data Tellingen StatBel'[Leeftijd])
VAR VorigeLeeftijd = MAX('Ruwe data Tellingen StatBel'[Leeftijd]) - 1
VAR HuidigGeslacht = MAX('Ruwe data Tellingen StatBel'[Geslacht 0/1])
VAR HuidigGemeente = MAX('Ruwe data Tellingen StatBel'[Hoogste Refnis-code van gemeente])
VAR CohorteHuidig = HuidigeLeeftijd&"_"&HuidigGeslacht&"_"&HuidigGemeente
VAR CohorteVorig = VorigeLeeftijd&"_"&HuidigGeslacht&"_"&HuidigGemeente
VAR AantalHuidig = CALCULATE(
    SUM('Ruwe data Tellingen StatBel'[Aantal]),
    'Ruwe data Tellingen StatBel'[Cohorte (leeftijd_geslacht_stad)] = CohorteHuidig
)
VAR AantalVorig = CALCULATE(
    SUM('Ruwe data Tellingen StatBel'[Aantal]),
    'Ruwe data Tellingen StatBel'[Cohorte (leeftijd_geslacht_stad)] = CohorteVorig,
    SAMEPERIODLASTYEAR('Date'[Date])
)
VAR SurvivalRate = AantalHuidig/AantalVorig
RETURN
AVERAGEX(
    VALUES ('Date'[Year]),
    CALCULATE(
        SUM('Ruwe data Tellingen StatBel'[Aantal]),
        'Ruwe data Tellingen StatBel'[Cohorte (leeftijd_geslacht_stad)] = CohorteHuidig
    )
    /
    CALCULATE(
        SUM('Ruwe data Tellingen StatBel'[Aantal]),
        'Ruwe data Tellingen StatBel'[Cohorte (leeftijd_geslacht_stad)] = CohorteVorig,
        SAMEPERIODLASTYEAR('Date'[Date])
    )
)

Is someone able to explain what happens in the calculation of Average survival rate 2 when returning infinity? And even better, can someone adapt the measure so it returns for example 104.86% for cohort 1_0_44084?

Thanks in advance

Kenneth

[kegoosse]

@tamerj1 - The Dax-code has been added for a while.
Do you have some other advice perhaps? The question has been online for a while, without any feedback. I assume I need to simplify the situation to get help?

[tamerj1]

@kegoosse 

Apologies for that. I haven't been notified for this change. You may try the following 

Average survival rate 2 =
AVERAGEX (
    VALUES ( 'Date'[Year] ),
    CALCULATE (
        VAR HuidigeLeeftijd =
            MAX ( 'Ruwe data Tellingen StatBel'[Leeftijd] )
        VAR VorigeLeeftijd =
            MAX ( 'Ruwe data Tellingen StatBel'[Leeftijd] ) - 1
        VAR HuidigGeslacht =
            MAX ( 'Ruwe data Tellingen StatBel'[Geslacht 0/1] )
        VAR HuidigGemeente =
            MAX ( 'Ruwe data Tellingen StatBel'[Hoogste Refnis-code van gemeente] )
        VAR CohorteHuidig = HuidigeLeeftijd & "_" & HuidigGeslacht & "_" & HuidigGemeente
        VAR CohorteVorig = VorigeLeeftijd & "_" & HuidigGeslacht & "_" & HuidigGemeente
        VAR AantalHuidig =
            CALCULATE (
                SUM ( 'Ruwe data Tellingen StatBel'[Aantal] ),
                'Ruwe data Tellingen StatBel'[Cohorte (leeftijd_geslacht_stad)] = CohorteHuidig
            )
        VAR AantalVorig =
            CALCULATE (
                SUM ( 'Ruwe data Tellingen StatBel'[Aantal] ),
                'Ruwe data Tellingen StatBel'[Cohorte (leeftijd_geslacht_stad)] = CohorteVorig,
                SAMEPERIODLASTYEAR ( 'Date'[Date] )
            )
        VAR SurvivalRate = AantalHuidig / AantalVorig
        RETURN
            CALCULATE (
                SUM ( 'Ruwe data Tellingen StatBel'[Aantal] ),
                'Ruwe data Tellingen StatBel'[Cohorte (leeftijd_geslacht_stad)] = CohorteHuidig
            )
                / CALCULATE (
                    SUM ( 'Ruwe data Tellingen StatBel'[Aantal] ),
                    'Ruwe data Tellingen StatBel'[Cohorte (leeftijd_geslacht_stad)] = CohorteVorig,
                    SAMEPERIODLASTYEAR ( 'Date'[Date] )
                )
    )
)

[kegoosse]

@tamerj1 

Thanks for the help. The suggested formula however returns the same result as the original one, i.e. correct values when reporting for a specific year, but returning infinty for the total value. Do you have any other suggestions?

[tamerj1 · Accepted solution]

Hi @kegoosse 
Please try with DIVIDE function

Average survival rate 2 =
AVERAGEX (
    VALUES ( 'Date'[Year] ),
    CALCULATE (
        VAR HuidigeLeeftijd =
            MAX ( 'Ruwe data Tellingen StatBel'[Leeftijd] )
        VAR VorigeLeeftijd =
            MAX ( 'Ruwe data Tellingen StatBel'[Leeftijd] ) - 1
        VAR HuidigGeslacht =
            MAX ( 'Ruwe data Tellingen StatBel'[Geslacht 0/1] )
        VAR HuidigGemeente =
            MAX ( 'Ruwe data Tellingen StatBel'[Hoogste Refnis-code van gemeente] )
        VAR CohorteHuidig = HuidigeLeeftijd & "_" & HuidigGeslacht & "_" & HuidigGemeente
        VAR CohorteVorig = VorigeLeeftijd & "_" & HuidigGeslacht & "_" & HuidigGemeente
        VAR AantalHuidig =
            CALCULATE (
                SUM ( 'Ruwe data Tellingen StatBel'[Aantal] ),
                'Ruwe data Tellingen StatBel'[Cohorte (leeftijd_geslacht_stad)] = CohorteHuidig
            )
        VAR AantalVorig =
            CALCULATE (
                SUM ( 'Ruwe data Tellingen StatBel'[Aantal] ),
                'Ruwe data Tellingen StatBel'[Cohorte (leeftijd_geslacht_stad)] = CohorteVorig,
                SAMEPERIODLASTYEAR ( 'Date'[Date] )
            )
        VAR SurvivalRate =
            DIVIDE ( AantalHuidig, AantalVorig )
        RETURN
            SurvivalRate
    )
)

[kegoosse]

@tamerj1 - Thanks, this version worked as desired!

[tamerj1]

Hi @kegoosse 

would you please copy/pase the dax code?