issues with custom hierarchy

[pBur]

Hi guys,

I am a newbie in Power BI world... And I am having troubles with hierarchy for Profit and Loss statement...
Dataset comes from two sources (DB and Excel) and mashes different types of account records into groups. However the formatting of the result should look like this:

Sales Income SUM(Type 1, Type 2,Type 3,Type 4,)
    Type 1
    Type 2
    Type 3
    Type 4
Operational Cost SUM(Type 5....11)
   Type 5
   Type 6
   Type 7
   Type 8
   Type 9
   Type 10
   Type 11
Financial income SUM(12..16)
   Type 12
   Type 13
   Type 14
   Type 15
   Type 16
Financial Expenses SUM(17..21)
   Type 17
   Type 18
   Type 19
   Type 20
   Type 21
Calculation 1 SUBTOTAL(1...21)
   Value 1       number 1
Calculation 2 SUBTOTAL(1...21, result1)
   Value 2       number 2
Calculation 3 SUBTOTAL(1...21, result 1, number 2)

EBIT SUM(Calculation 1, Type 17, Type 14, Type12, Type 7)

In Pivot SUM and SUBTOTAL items would show on top easily, however this is not what I need. And I haven't find a solution in Power BI yet...

Maybe someone has any suggestions on how to overcome this? Let me know if it's clear what I want to achieve 🙂

EDIT: this needs to be in ONE table as this info will need to be exported and stored.

[dkay84_PowerBI · Accepted solution]

I'm not sure Power BI (or Power Pivot in Excel) is the best way to create a P&L.  Maybe do the data mashup in Get and Transform (power query) in excel but then build your P&L in the worksheet as it gives you more flexibility to do what you want.  Plus if your goal is simply to export the table, then why use Power BI, which is for authoring reports/visuals?

[pBur · Accepted solution]

In case anyone will encounter such an issue - I've found a way to kind of solve it. Once you've set up the data model in PowerBI, publish it to a workspace. Then use 'Analyse in Excel' function. Once .ocb is downloaded make a pivot table with the data you need (including filters) then the magic starts - you can convert Pivot into formulas (PivotTable Tools - Analyze - OLAP Tools - Convert To Formulas) - excel imports the dataset as a CUBE. Then manipulation of data and custom report building gets quite easy. Detailed explanation here: https://www.youtube.com/watch?v=JYhYLKnQucc

[dkay84_PowerBI]

Thanks for this reference.  I did not know about converting pivots into cubes.

Another option is referenced here, but with the Analyze in Excel functionality, it's not really necessary:

http://exceleratorbi.com.au/excel-workbook-connection-local-power-bi-desktop/

[dkay84_PowerBI · Accepted solution]

I'm not sure Power BI (or Power Pivot in Excel) is the best way to create a P&L.  Maybe do the data mashup in Get and Transform (power query) in excel but then build your P&L in the worksheet as it gives you more flexibility to do what you want.  Plus if your goal is simply to export the table, then why use Power BI, which is for authoring reports/visuals?