cancel
Showing results for
Did you mean:
Highlighted
Helper I

## XNPV Aggregate vs Individual

Hi guys-

I have data that looks like this

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

## 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.

3 REPLIES 3
Highlighted
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

Proud to be a Super User!

Check out my blog:

Power BI em Português

Highlighted
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

## 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.

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