Creating a Normalized Index based on % Changes

[askelton]

I'm fairly new to PowerBI and am trying to figure out how to do this.  I've done quite a bit of googling and couldn't find a solution this is issue.  I have a calculated table in PowerBI similar to columns F/G (Or a measure that outputs that percent as well if it is easier).    I would like to calculate columns J to be able to graph, to tell a better overall picture than graphing G.  This is easy in excel, make the first column 100, and then for every next row do, (the last row) * (1+ Weighted % Change).  However after about 4-5 hours of trying and researching I couldn't figure out how to get this to work in PowerBI.

[Anonymous · Accepted solution]

Hi @askelton,

You can refer to below formulas to calculate current row with recursive previous row:

Create a new table with original data and new row(first date):

Merged = 
var temp= FIRSTDATE(Table1[Month])
return
UNION(ROW("Month",DATE(YEAR(temp),MONTH(temp)-1,DAY(temp)),"Weight % Change",0),Table1) 

Measures:

Default Amount = if(MAX([Month])=FIRSTDATE(ALL(Merged[Month])),100)

Multiply Percent = 
IF (
    ISBLANK ( [Default Amount] ),
    1 + MAX(Merged[Weight % Change]),
    [Default Amount]
)

Cumulated LN = 
CALCULATE (
    SUMX ( FILTER(ALL(Merged),[Month]<=MAX(Merged[Month])), LN ( [Multiply Percent] ) )
)

Recursive Calculations = 
SUMX (
    VALUES ( 'Merged' ),
    IF ( ISBLANK ( [Default Amount] ), EXP ( [Cumulated LN] ), [Default Amount] )
)

Reference link:

Recursive Calculations in PowerPivot using DAX

LN Function

EXP Function

PRODUCTX Function (DAX)

Regards,

Xiaoxin Sheng

[MFelix]

Hi @askelton,

I believe that you need something similar to what i posted in this discussion:

community.powerbi.com/t5/Desktop/Waterfall-chart-adding-percentages-up-in-total-column/m-p/163027#M71020

I'm not at the computer but try to do the necessary changes and if you need any assistance please tell me.

Regards

MFelix