Welcome to another Power BI tutorial! In this video, we will show you how to create hierarchical data using DAX and how to use this data to create reports in Power BI.
We will start by using an Excel file with four columns: ID, Account, Parent, and Amount. This is a simple Chart of Accounts where each account has its own unique ID, and the parent column contains the ID of the parent account.
We will use this hierarchical chart of accounts to create a report in Power BI. To do this, we will import the data using the "Get Data" feature and select the Excel workbook as the data source. Once the data is loaded, it will appear in the data tab with all the available fields.
Next, we will go to the data view to create hierarchical data for our report using DAX. There are many functions in DAX to work with hierarchical data. We will combine the ID and the Account Title to create a Full Account column and create delimited paths using the ID and the Parent field in the Path function.
We will also use the path length function to find out the length of the path and the path item function to get any item from a hierarchical path. However, we want the Account Title, not the Path item, which is the Account ID. To get the Account Title, we will use the lookup value function.
Using the same DAX formula, we will create the remaining four levels. We will just change the path item number in each formula.
Join us in this tutorial and learn how to create hierarchical data using DAX and Power BI to create reports that will help you gain insights into your business!
https://virtualschool.org/tutorial?v...
Link to the Source Data and Power BI File: https://drive.google.com/file/d/1xFfK...
#PowerBI
#DAX
#hierarchicaldata
#accounting
#reporting
#balancesheet
#incomestatement
#profitandloss
Chapters:
00:15 Hierarchical Data
01:12 Importing Data into Power BI Desktop
01:57 Using DAX to Create Hierarchies
03:10 Using DAX to Create Levels
05:05 Create a Report from Hierarchical Data
08:00 Create a SubReports from Hierarchical Data