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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
plbeauchamp
Regular Visitor

Measure for IRR by Hierarchy

 

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!

 

Project ERD.PNG
Sample Data.PNG

4 REPLIES 4
OwenAuger
Super User
Super User

@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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

@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!

 

AggIRR proper.PNG

 

Picture with error

 

AGGIRRerror.PNG

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

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

 

IRR Division.PNG

 

Drilled into projects

 

IRRProjectError.PNG

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.