Relative Growth Rate (Evolution Index) Calculation

[Chitemerere]

I am trying to calculate a relative growth rate or so called evolution index as per the following:

I have columns which are calculated measures.  These are 'MAT Dispensed Amount LY' and 'MAT Dispensed Amount CY'.  MAT is moving annual total, CY is current year and LY is last year.  I also have another column which is '% Growth MAT Amount CY' which is growth in the MAT over the last year.  The measures are calculated as follows:

MAT Dispensed Amount CY = CALCULATE([Total Dispensed Amount],

DATESINPERIOD(AuditCalendar[Date], MAX(AuditCalendar[Date]), - 1, YEAR))

MAT Dispensed Amount LY = CALCULATE([MAT Dispensed Amount CY],

SAMEPERIODLASTYEAR(AuditCalendar[Date]))

% Growth MAT Amount CY = [MAT Dispensed Amount CY]/[MAT Dispensed Amount LY] - 1

I then try to calculate the 'EI CY Amount' which is the Evolution Index (EI). The Evolution Index is basically the growth rate of a product relative to the growth rate of the market.

For example, the EI for the product Calcigard should be (20.52/31.66)*100 = 64.81.  The EI for the market is always 100 i.e. (31.66/31.66)*100.  A product having an EI above 100 means it is growing above the overal market and conversely.

My calculaton for the EI which i have tried is as follows:

EI CY Amount = (100 + [% Growth MAT Amount CY])/

(100 + [Total MAT Amount CY]/[Total Dispensed Amount LY] -1)*100

with the other two measures as follows:

Total MAT Amount CY =

CALCULATE([MAT Amount CY], ALLSELECTED(tblDatawarehouseConsolidatedCleanStage))

Total MAT Amount LY =

CALCULATE([MAT Amount LY], ALLSELECTED(tblDatawarehouseConsolidatedCleanStage))

The table for the data is tblDatawarehouseConsolidatedCleanStage

The calculation is obviously wrong as per the example given above and the market EI is 89.6 but should the 100.

Can someone assist with the calculation of the right EI.

Best regards,

Chris

[v-lid-msft · Accepted solution]

Hi @Chitemerere ,

we can create a measure to get the EI CY Amount to meet your requirement.,

EI CY Amount = DIVIDE([% Growth MAT Amount CY],CALCULATE([% Growth MAT Amount CY],ALLSELECTED())) *100

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that we have shared?

BTW, pbix as attached.

[Chitemerere]

Thank you very much for the assistance, most appreciated.

Best regards,

Chris