Tag Archives: hierarchy

Percent of parent in hierarchy (DAX)

Posted on by • Posted in Advanced • Tagged , , • 1 Comment

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…

Hierarchy testing in DAX (attempt number 2)

Posted on by • Posted in Advanced • Tagged , , , , , , • Leave a comment

Here is some new query samples on how DAX hierarchies work. And this time it will work 🙂

Id did a little modification of the model. I had to make a new hierachy in the employee table.

Hierachy in tabular model

Then you will have to deploy the model to the server.

My sample query looks like this:

DEFINE
    MEASURE 'Employee'[Employeelevel] =
        PATHLENGTH (
            PATH ( Employee[EmployeeKey], Employee[ParentEmployeeKey] )
        )
    MEASURE 'Employee'[ParentEmployee] =
        LOOKUPVALUE (
            'Employee'[Last Name],
            'Employee'[EmployeeKey], PATHITEMREVERSE (
                PATH (
                    'Employee'[EmployeeKey],
                    'Employee'[ParentEmployeeKey]
                ),
                2,
                1
            )
        )
    MEASURE 'Employee'[PathReverse] =
        PATHITEMREVERSE (
            PATH (
                'Employee'[EmployeeKey],
                'Employee'[ParentEmployeeKey]
            ),
            2,
            1
        )
    MEASURE 'Employee'[Path] =
        PATH (
            'Employee'[EmployeeKey],
            'Employee'[ParentEmployeeKey]
        )
EVALUATE
FILTER (
    SUMMARIZE (
        'Employee',
        'Employee'[EmployeeKey],
        'Employee'[Last Name],
        "Sales", 'Reseller Sales'[Reseller Total Sales],
        "Path", 'Employee'[Path],
        "PathItemReverse", 'Employee'[PathReverse],
        "Employeelevel", 'Employee'[Employeelevel],
        "ParentEmployee", 'Employee'[ParentEmployee]
    ),
    ISBLANK ( 'Reseller Sales'[Reseller Total Sales] ) = FALSE
)
ORDER BY 'Employee'[ParentEmployee], 'Employee'[EmployeeKey]

And the result looks like this

hierarchy correct

To verify the result I made an Excel pivot.