cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

XNPV Aggregate vs Individual

Hi guys-

 

I have data that looks like this

 

example.PNG

 

In the XNPV Column, I am using this formula with 10% (.1) as the discount rate

 

Column = xnpv('Table 1', 'Table 1'[Profit], 'Table 1'[Dates], .1)

 

My problem:  The XNPV is shown as a sum of the XNPVs of all the projects. I want it to be for the individual project, so specific to that individual row.

 

Any suggestions?

 

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Helper I
Helper I

Re: XNPV Aggregate vs Individual

Alright guys- figured out a way that worked for me. Had to split numerator and denominator to make it work.

 

Made two columns one like this

 

XNPV DENOMINATOR = power(1.1,('Table'[Dates]-TODAY())/365)

 

= (1+i)^((Future Date-today)/365)

 

XNPV = CALCULATE(sum('Table'[Margin]))/'Table'[XNPV Denominator]

 

Not the fastest work around but works and got me what I needed.

 

Hope somebody else comes across this while browsing Google or something. This is pretty straightforward and considering we are discussing BI software, surprised that this does not have more optionality.

 

View solution in original post

3 REPLIES 3
Highlighted
Super User V
Super User V

Re: XNPV Aggregate vs Individual

Hi @charles_o,

 

Try to createthe following measure:

 

XNPV = CALCULATE( SUM(Table[Profit]) * (0,1))

Since this is a measure it's based on context so on the project level it will give you the the discount value.

 

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





Highlighted
Helper I
Helper I

Re: XNPV Aggregate vs Individual

The discounted valued needs to take into account the date- else I would just multiply Profit*(1-.1)

 

The npv formula as written out on paper for example would be like this:

 

Profit/((1+.1)^((Date- todays_date)/365))

 

so for example if I plan to make $100 a year from today:

 

$100/((1+.1)^((2/13/2019-2/13/2018)/365)

 

$100/((1+.1)^(365/365)

 

$100/(1.1) = $90

 

But if it is half a year from today it would be this

 

$100/(1.1)^(182.5/365)

 

$100/(1.0488)= $95.346

 

 

 

 

 

 

 

Highlighted
Helper I
Helper I

Re: XNPV Aggregate vs Individual

Alright guys- figured out a way that worked for me. Had to split numerator and denominator to make it work.

 

Made two columns one like this

 

XNPV DENOMINATOR = power(1.1,('Table'[Dates]-TODAY())/365)

 

= (1+i)^((Future Date-today)/365)

 

XNPV = CALCULATE(sum('Table'[Margin]))/'Table'[XNPV Denominator]

 

Not the fastest work around but works and got me what I needed.

 

Hope somebody else comes across this while browsing Google or something. This is pretty straightforward and considering we are discussing BI software, surprised that this does not have more optionality.

 

View solution in original post

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors