Marco Russo, BI expert from sqlbi.com, 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.