Percent of parent in hierarchy (DAX)

Getting percent of parent in a dimension without a hierarchy is quite simple. But to get percent of parent in a hierarchy with several levels you will have to add some more code. In the current version of DAX you will have to write code for each level.

My sample query will only work for the hierarchy in the “Geography” dimension called “Geography”.

Geography 1

Then I will add 2 calculated measures to my model.

ParentSalesEmployee :=
IF (
    ISFILTERED ( 'Geography'[City] );
    CALCULATE (
        SUM ( [Order Quantity] );
        ALL ( 'Geography'[City] )
    );
    IF (
        ISFILTERED ( 'Geography'[State Province Name] );
        CALCULATE (
            SUM ( [Order Quantity] );
            ALL ( 'Geography'[State Province Name] )
        );
        CALCULATE (
            SUM ( [Order Quantity] );
            ALL ( 'Geography'[Country Region Name] )
        )
    )
)

And then

Ratio To Parent:=Sum([Order Quantity]) / [ParentSalesEmployee]

And the result will looks like this:

Geography 2

I hope hierachy handling will become better in next version of DAX…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s