Create an in-memory pivot table using DAX Path functions

[NV]

There is no easy way to sum up the content of this post in one header sentence, but at least I gave it a shot. The purpose of this post is to demonstrate a DAX-measure that will allow a pivot table-like functionality inside Power BI using the matrix or table visualization. 

This measure was borne from trying to display a simple P&L statement in Power BI, just to realize that there was no way to neatly display the figures and calculated sub-totals in a matrix or table in Power BI. Excel obviously does not have this limitation as the figures can be displayed in a simple pivot table with sub-totals, alongside aggregates and graphs from the same data model. You could of course aggregate the statements in the data model and display the statement as is, but that would not be subject to filters as the aggregates would reside on row level in the data model. I needed to find a solution to have this working in dynamic fashion. The solution was in the end quite simple, so I thought I would share it with you.

This post will describe the solution for creating a P&L-statement, but the same rules could apply for any data. Basically, all we need is a dimension with parent-child relationships between the members, that you want to sum up on parent levels.

In this case the chart of accounts (COA) will serve the purpose for the parent-child relations. The COA lists parent accounts and child accounts. The transactions are usually recorded on the lowest level of the parent-child hierarchy as it is in this case. For reporting purposes we can add accounts for the relevant sub-totals we like to display in our statement table (ex. Gross Profit, EBITDA, EBIT etc.). Many charts of accounts being used already have parent ids for the accounts ready, so it's an ideal candidate for this demo. The below image displays a simple setup for the parent-child relations.

The above sample statement would have EBIT on the top of the hierarchy. Going down the hierarchy EBITDA has EBIT as parent, Gross Profit has EBITDA as a parent, Sales Profit has EBITDA as a parent and together they form a hierarchy 4 levels deep. The parent is always the account into which you want to sum up the account in question. Same rules apply for the remainder of the accounts.

The parent account and the parent-child path of the account are listed in the below Power BI data model. The Path-column is calculated as 

Path = PATH([ID];[ParentID])

When the parent-child accounts have all been setup the formula for getting the calculation for the "in-memory pivot table" is:

PivotAmount =
SUMX (
    Accounts;
    CALCULATE (
        SUM ( Facts[Amount] );
        FILTER (
            ALL ( Accounts );
            PATHCONTAINS ( Accounts[Path]; EARLIER ( Accounts[ID]; 1 ) )
        )
    )
)

‘Facts’ is the table where all the transactions are stored and ‘Accounts’ is the table with the list of accounts and parent id’s.

SUMX moves the calculation into row context which allows us to use the EARLIER function inside the PATHCONTAINS function to get all the account ID’s which should be summed up under each parent account. To then calculate for instance EBIT or EBITDA we can simply use:

EBIT = CALCULATE([PivotAmount];Accounts[Name]="EBIT")
or
EBITDA = CALCULATE([PivotAmount];Accounts[Name]="EBITDA") 

Same applies for all accounts and subtotals in the COA.

Putting the Pivot Amount and the name column in a Power BI table we get the correct subtotal amounts, but we might not be satisfied with the layout of the table.

First of all we need some sort of indenting to visually display which accounts are added in which sub-totals and we also need some sort of filtering option to hide and display leaf-levels of each total row. 

For this I used a different parent-child hierarchy (ViewParentID) from which I also derived a filter which refers to the depth of the new hierarchy. As the below table displays, the new path (ViewPath) is much simpler and filtering by NodeViewDepth will display only the accounts you want to display on each level.

I also created indents based on NodeDepthView to get the Account & Name column that helps the reader keep track on the level of each displayed account.

I also gave the NodeDepthView values English names to be used in slicers:

1 = “SHORT P&L”

2 = “& Sub-total 1”

3 = “& Sub-total 2”

4 = “& Sub-total 3”

The result can be seen below. Using a slicer we can get pivot-like functionality in the table visualization within Power BI. For all dim tables we like to filter on (below the Date Table), we need to make sure the relationships in the data model work only one way as opposed to both ways. Otherwise the parent accounts that do not have recorded transactions and therefore no dates will automatically be filtered out when filtering on dates.

Top level:

1st level:

2nd level:

There we have it. A pivot table using a DAX-measure rather than an application built pivot table. Note that the SUMX function can tend to take a toll on processing, which is the only drawback of the measure I’ve noticed so far. If you have ideas on how to improve the solution, please comment.

[marsclone]

I've tried this solution for creating a P&L but got stuck at this step:

"When the parent-child accounts have all been setup the formula for getting the calculation for the "in-memory pivot table" is:

PivotAmount =
SUMX (
    Accounts;
    CALCULATE (
        SUM ( Facts[Amount] );
        FILTER (
            ALL ( Accounts );
            PATHCONTAINS ( Accounts[Path]; EARLIER ( Accounts[ID]; 1 ) )
        )
    )
)

‘Facts’ is the table where all the transactions are stored and ‘Accounts’ is the table with the list of accounts and parent id’s".

Do i have to put this Formula in a new Table or in a new Measure?

When i create a new Table i get the message "the expression specified in the query is not a valid table expression", when i create a new measure it only shows the Grandtotal of all lines.

Test

I find it really hard to create a P&L in Power BI, while for our organisation this report is one of the important reports available.

Are there other solutions for creating (sub)totals in a P&L?

Thank you in advance.

[Anonymous]

Hi,

Nice to finally find someone who is trying to tackle a Financial P/L with DAX.

Do you have a version of the .pbix that you can share??

/Niels

[jordi012]

Hi,

I have a numbe of questions...that looks cool, but I have tried to reproduce your steps and I didnt get the same results...actually using the pivot measure didnt get the proper order of the accounts...is there anything that I missed?? Would that be possible to send me, please, a template? I need to build this in Power BI and I cant..Thank you.