Forum Discussion
issues with custom hierarchy
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.
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?
- 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
3 Replies
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?
- 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
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/