QUESTION ? What is the best Approach given this Account Dim ? How to Build an Income statement

[THEG72]

Hi Community,

I was looking for the best approach when building a cascading total and displaying in Power BI an income statement

In Excel, it was fairly easy i must say!

I have a dataset with the following Account dimension table config.

Account Dimension Table

I can see useful fields like:

1. Account Types (shown as H for header/Detailed posting account)
2. Account Levels
3. Is totals 
4. Parent and Child ID relationships

My journal facts are as follows and related via AccoutID.

Fact Journals

Thanks again for your advice! 

[v-chuncz-msft · Accepted solution]

@THEG72,

Here is an article talking about parent-child hierarchies in DAX for your reference.

http://www.daxpatterns.com/parent-child-hierarchies/

[THEG72]

Hi @v-chuncz-msft

Thanks for the reference. The example provided doesnt really provide much assistance with my example where i have a predefined accounts table with parent, child, level and header codes already setup.

Can you advise how the path statement would work in this example with this table data structure below?

I have created a custom column to showing "GroupHeadeLevelType". H for Header , 1 for level and then I for income.

Revenue Account Structure

The path structure needs the Parent and Account to have same data "Values in parent_columnName must be present in ID_columnName. That is, you cannot look up a parent if there is no value at the child level"

In this case the Parent will never be in the child level, How do i fix this? For example Revenue has a Parent account of 28, this number never appears in Child.

Thanks again for any assistance!

The Accounts structure is shown below and i cant see ParentAccountID code 1 does not appear in child AccountID. So Path returns that error. Same with AccountID 28 for which is a header account for Revenue shown below in control groups.

Account DimensionsCONTROL GROUP HEADERS

after a bit of playing around i merged the Parent into Accountid and then removed the duplicates and path works now...hoping to follow the rest of the guides thanks again.

PATH EXAMPLE