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), (Product / Prices), 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
Hi Corentin,
Welcome to our forum! Unfortunately, Power BI is not designed to generate P&L statements, which means this task is not simple. You can refer to this post for some ideas. However, if you're new to Power BI/Power Pivot, I would probably do this in Excel instead.
Mynda
Hi Mynda,
Thank you very much for your answer!
Well when I said I'm new to Power BI, I've known Power Pivot and Query for about 2 years now.
I understand it is not the easiest, but I reckon it's possible. Or to put it in another way, the task I've been asked to complete seems really complicated in excel, because it requires a lot of flexibility which Power Pivot/BI offers.
From many inputs, I'm required to build a PnL, with different views per Region, per Businesses.
In my measures I used recurrence function to see make it vary with time, such as the effect of productivity on Cost.
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
"I understand it is not the easiest, but I reckon it's possible. Or to put it in another way, the task I've been asked to complete seems really complicated in excel, because it requires a lot of flexibility which Power Pivot/BI offers."
Actually, I would say it's the opposite. Excel is super flexible, Power Pivot/BI is not.
If you're confident with DAX then by all means, use the techniques at the link I provided above.
Again thank you for your insight Mynda.
I am very grateful to have it, since I've leverage quite a few tips you provided in your videos.
If you think excel is more flexible, I do believe you.
Furthermore, if you have any articles, topic on this forum or videos to recommend that could help building such a valuation model I'd be more than grateful.
I value very much the flexibility that pivot tables offers, to select different views with slicers, but I think I'd be stuck with the same issues.
Best,
Corentin
Hi Mynda,
I think I've partially nailed it with the SWITCH function and a chart of account table.
Although my totals and subtotal are not really getting it right.
Corentin
