Measure for IRR by Hierarchy

[plbeauchamp]

Hello,

I am am trying to get a measure to create a simple graph that shows IRR by division then drills down into IRR by project and then IRR by status. I have included my relationship diagram and sample data from my two fact tables.  The measure I currently have only works if you filter for an individual project

 The formula for the measure you see in the ERD below is 

AggIRR = XIRR(Fact_CashFlow,Fact_CashFlow[Cash_Flow_Annual],Fact_CashFlow[Date],.1)

Any help is appreciated!

[OwenAuger]

@plbeauchamp

The measure you posted worked for me when I created a replica of your Fact_CashFlow table.

If I filtered on a single Project_ID, I got the IRR for that project (using the Cash_Flow_Annual values), and if I selected multiple Project_ID values, I got the IRR for the total cashflow across projects. So XIRR must automatically aggregates cashflow by date.

What result were you getting when you selected more than one project, and what did you expect?

Also, did you want the CapEx values in Fact_Project to be included in the XIRR calculation? If so we would need to write a slightly different measure.

Regards,

Owen

[plbeauchamp]

@OwenAuger  Thanks for the reply.

The measure does work if I have a single card visual.  For instance, if I add a slicer for division and select division A it will show me the aggregate IRR for all of the projects in division A on a single card visual.

What I am looking for is a visual like this one.  This is the same measure and it works for a small data set for the visual of a column chart that starts as IRR by division then drills into IRR by project.  For some reason though when I scale my dataset up to a few hundred rows, it no longer works and gives me the error in the second picture. The capex values did not need to be incluced.  Thank you!

Picture with error

[OwenAuger]

Interesting...

I experimented myself with dummy data and managed to get the same error as you in some cases, and in other cases "The XIRR function couldn't find a solution."

Suggestions:

1. This may not be an issue, but I suggest redefining your measure so that distinct dates are passed to XIRR. I'm not sure if this matters but I think it makes the measure more efficient if nothing else.
   

   AggIRR =
   XIRR (
       VALUES ( Fact_CashFlow[Date] ),
       CALCULATE ( SUM ( Fact_CashFlow[Cash_Flow_Annual] ) ),
       Fact_CashFlow[Date],
       .1
   )

2. Then I would suggest finding the problematic projects with a measure like:
   

   IRR Error Flag =
   VAR TryIRR = [AggIRR]
   RETURN IF ( ISERROR ( TryIRR ), 1 )

   Put this in a visual broken down by Project to find the problematic ones, then maybe test whether Excel can calculate the IRR for those projects. There might be an obvious problem with cashflows that makes IRR unsolvable in some cases.

I imagine your data may be too sensitive to share, but if you can share something santised that exhibits the error I could take a closer look.

Regards,

Owen

[plbeauchamp]

@OwenAuger      Thank you.  Both of these measures were helpful.  I was able to clear all projects that had an error in cashflow using the error flag.  I am very close now as the visual for aggregate IRR by division is working (picture 1).  It only works however if I apply a visual level filter which only includes the divisions with projects in the cash flow table (not every division in the division table has a project with a cashflow).  Once I drill down from the division into the project level, I get the error in the second picture.  It seems this is an issue where the measure does not always make sense in certain contexts.  Is there a way to incorprorate some logic into the measure to where it will only do the calc based on relevant critieria?  I would be glad to share a sample dataset producing the errors but am unsure the best way to get it to you.  It is in an excel workbook.

Drilled into projects