Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
askelton
Resolver I
Resolver I

Creating a Normalized Index based on % Changes

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.

 

Index.PNG

 

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

Hi @askelton,

 

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

 

Capture2.PNG

 

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

 

 

Capture.PNG

 

Reference link:

Recursive Calculations in PowerPivot using DAX

LN Function

EXP Function

PRODUCTX Function (DAX)

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

2 REPLIES 2
v-shex-msft
Community Support
Community Support

Hi @askelton,

 

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

 

Capture2.PNG

 

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

 

 

Capture.PNG

 

Reference link:

Recursive Calculations in PowerPivot using DAX

LN Function

EXP Function

PRODUCTX Function (DAX)

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
MFelix
Super User
Super User

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

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.