Forum Discussion
Solved
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 a...
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
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/