Forum Discussion

plbeauchamp's avatar
plbeauchamp
Regular Visitor
8 years ago

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!

 


Need Help

4 Replies

  • OwenAuger's avatar
    OwenAuger
    Icon for Super User rankSuper User
    8 years ago

    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's avatar
      plbeauchamp
      Regular Visitor
      8 years ago

      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's avatar
        OwenAuger
        Icon for Super User rankSuper User
        8 years ago

        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