How to Calculate with Hierarchies in DAX?

10 December 2013 · 5 comments · 2916 views

Loading the player...

If this message stays on, check that you have the latest Flash Player installed ( and that your browser supports JavaScript.

Data Analysis Expressions

Marco Russo, BI expert from, discusses ratio-to-parent measures, which use DAX hierarchies, in this short video. You will see how to create such a calculation and how to use it in a Pivot Table (00:30). DAX does not have an internal mechanism for hierarchy navigation, so you need to write the appropriate DAX code in order to obtain the desired results. This technique is available as an Excel feature of the PivotTable, but it requires creating the same calculation in every PivotTable. By creating the measure in the data model itself the calculation becomes available to all pivot tables.

The measure uses a formula (01:00), which contains a series of nested IF statements, checking the result of ISFILTERED for each table column that corresponds to a level of a hierarchy, starting at the bottom. Once the selected level has been detected, a DAX expression is evaluated in order to return the proper calculation of the ratio-to-parent measure. If you are interested in using hierarchies in DAX, including parent-child ones, and if you would like to learn how to perform other hierarchical calculations, make sure to watch Marco’s Hierarchies in DAX in-depth, 55-minute video tutorial.


markosomppi · 29 October 2014

Missing video?

Rafal Lukawiecki · 29 October 2014

Marko, the video is working when I tried it, but you may need to click on the “play” arrow showing on the screenshot at the top of this page in case you use an iOS browser, otherwise it should play automatically. If it does not, please email us at and we will troubleshoot it for you: we will not give up until it plays perfectly for you, I promise that.

Marco Russo · 29 October 2014

The video is at the beginning of this page - maybe you have an issue with your browser? Did you try another browser?

markosomppi · 29 October 2014

Working perfectly with Chrome…thanks ;)

Rafal Lukawiecki · 29 October 2014

Thanks for letting us know, Marko, but it should be working well with all major browsers, as it has been tested that way, too. I’ll drop you an email to find out more about your set-up, but I am glad you are able to watch the videos using Chrome.