topic Iterative average of growth ratio in DAX Commands and Tips

Iterative average of growth ratio

kegoosse — Fri, 28 Oct 2022 12:51:46 GMT

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

Re: Iterative average of growth ratio

tamerj1 — Fri, 28 Oct 2022 12:44:26 GMT

Hi kegoosse

would you please copy/pase the dax code?

Re: Iterative average of growth ratio

kegoosse — Wed, 02 Nov 2022 15:59:46 GMT

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?

Re: Iterative average of growth ratio

tamerj1 — Wed, 02 Nov 2022 17:02:00 GMT

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

Re: Iterative average of growth ratio

kegoosse — Thu, 03 Nov 2022 09:10:05 GMT

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?

Re: Iterative average of growth ratio

tamerj1 — Thu, 03 Nov 2022 09:25:57 GMT

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

Re: Iterative average of growth ratio

kegoosse — Thu, 03 Nov 2022 10:01:18 GMT

tamerj1 - Thanks, this version worked as desired!