Power BI for Accountants

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

Organise measure for PnL projections

  • 1.  Organise measure for PnL projections

    Posted Dec 04, 2020 04:43 AM

    Hello,

    I'm pretty new to Power BI, I used to use Power Pivot before. 

    I have a task which requires to build a PnL, and forecast the future cash flows.
    I have used matrix, where I inputs my assumptions (product / growth rate)  to compute my measures.
    My issue is in organising the final output like in a normal PnL where accounts aggregate in higher account hierarchy, such as in this example:

                                Year 1             Year2                    Year3
    Revenues
                  Product 1
                  Product2
    Cost
         COGS
                 Product1
                 Product 2
           Transport
                Product 1
                Product2

    Where here, Revenues, Cost, COGS, transport are measures. The problem I have is when I use a pivot table and put the measures horizontally, they all end up in the same column or they don't aggregate in higher account, looking like this:

                                Year 1             Year2                    Year3
    Revenues
                  Product 1
                  Product2
    Cost
                 Product1
                 Product 2
    COGS
                 Product1
                 Product 2
    Transport
                Product 1
                Product2

    I know, that I wan leverage power query to unpivot this table categorise the accounts and then repivot it. But I would lose the formulas in the measures, and I won't be able to play around with the assumptions and see how it affects my PnL.

    Do you have any idea how I could pull this off ? 

    Thank you. 

    Corentin



    ------------------------------
    corentin Bodros
    ------------------------------



  • 2.  RE: Organise measure for PnL projections

    Bronze Contributor
    Posted Dec 07, 2020 12:58 PM
    Corentin,

    This sounds like a model structure setup need.  Please review these two blog posts to make sure your data model structured correctly to view the PnL data. Do you have a Report Account Layout table that controls how GL account rollup?  This is usually something that is missing in some ERP systems and will need to be built.

    Build a P&L With Power BI - Excelerator BI

    Easy Profit & Loss (and other account) statements in PowerBI and Excel - Part2 – The BIccountant


    ------------------------------
    Barry Crowell
    BI/EDW Solution Architect
    KTL Solutions, Inc
    Frederick MD
    301-360-0001
    ------------------------------



  • 3.  RE: Organise measure for PnL projections

    Posted Dec 07, 2020 04:18 PM

    Hey Barry, 

    Thanks for your reply!

    The links you provided are very interesting, I think it's part of the answer. Because in the two example mentioned I think the data comes from a rawdata base. 

    Or to put it in another way, those PnL are based on a past set of data where the Revenues, Cost, etc. are not changing anymore. Whereas in my case it's a valuation model where the revenues, cost and all accounts are measures themselves, that should be able to vary with hardcoded assumptions that would be displayed in slicers, such as inflation, number of item sold, growth rate per year of the business, etc.

    Now, regarding the categorisation of measures, I've come accross different possibilities: 

            - Unpivot my pivot table, add a column where I categorise the accounts like a chart of account and repivot it again.
    The issue with this one is I believe that if I implement some slicer regarding the assumptions (such as inflation) I think I would have to refresh the model every time ? 

            - Then someone on another forum thought I could use PATH function or Custom Visuals ? I'm not sure whether in this case it could do the trick

    Thanks,

    Corentin



    ------------------------------
    corentin Bodros
    ------------------------------