DAX calculations with hierarchies: Set the order straight.

, 2017-05-10 (first published: )

[2017-Apr-30] If you have ever tried to create calculated measures in SSAS Tabular/ Power BI models where different levels of your model hierarchy had to be considered then you'd understand how DAX language doesn't have a straight way to identify parent & child levels in order to compute necessary metric results. There is a good article http://www.sqlbi.com/articles/clever-hierarchy-handling-in-dax/ by Alberto Ferrari where he explains a workaround approach to handle hierarchies in DAX.

I made one step further, based on my recent client project experience, and created a new example with one extra level for the Product dimension using Adventure Works Internet Sales tabular database. Here is the code to calculate Sales Ratio to a Parent level for all levels (Category, Sub Category, Model and Product Name)

RatioToParent:=
IF (
ISFILTERED ( Product[HCategory] ),
SUM ( 'Internet Sales'[Sales Amount] )
/ CALCULATE ( SUM ( 'Internet Sales'[Sales Amount] ), ALL ( Product[HCategory] ) ),
IF (
ISFILTERED ( Product[HSubcategory] ),
SUM ( 'Internet Sales'[Sales Amount] )
/ CALCULATE ( SUM ( 'Internet Sales'[Sales Amount] ), ALL ( Product[HSubcategory] ) ),
IF (
ISFILTERED ( Product[HModel] ),
SUM ( 'Internet Sales'[Sales Amount] )
/ CALCULATE (
SUM ( 'Internet Sales'[Sales Amount] ),
ALL ( Product[HModel] )
),
IF (
ISFILTERED ( Product[HProduct] ),
SUM ( 'Internet Sales'[Sales Amount] )
/ CALCULATE ( SUM ( 'Internet Sales'[Sales Amount] ), ALL ( Product[HProduct] ) )
)
)
)
)


However, when I looked at the results for this new RatioToParent measure using Excel, I saw some weird values: only the to Category level showed me correct calculations and all other Product hierarchy levels had 100%.


Then I changed the order of levels in my DAX calculated measure from (Category > Sub Category > Model > Product Name) to (Product Name >  Model > Sub Category Category):

RatioToParent_Ordered:=
IF (
ISFILTERED ( Product[HProduct] ),
SUM ( 'Internet Sales'[Sales Amount] )
/ CALCULATE ( SUM ( 'Internet Sales'[Sales Amount] ), ALL ( Product[HProduct] ) ),
IF (
ISFILTERED ( Product[HModel] ),
SUM ( 'Internet Sales'[Sales Amount] )
/ CALCULATE ( SUM ( 'Internet Sales'[Sales Amount] ), ALL ( Product[HModel] ) ),
IF (
ISFILTERED ( Product[HSubcategory] ),
SUM ( 'Internet Sales'[Sales Amount] )
/ CALCULATE (
SUM ( 'Internet Sales'[Sales Amount] ),
ALL ( Product[HSubcategory] )
),
IF (
ISFILTERED ( Product[HCategory] ),
SUM ( 'Internet Sales'[Sales Amount] )
/ CALCULATE ( SUM ( 'Internet Sales'[Sales Amount] ), ALL ( Product[HCategory] ) )
)
)
)
)

 and that made the whole difference and resolved the issue of miscalculated results:


So my learning lesson in this exercise was that order of hierarchical levels in DAX calculations is very important. Otherwise, calculations can only be correctly fulfilled for the top parent level of your tabular model hierarchy (please see the first DAX calculations). Lesson learned, mistakes not to be repeated!

Happy data adventures!

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

2009-02-23

1,567 reads

Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...

2009-02-17

1,530 reads

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.

2009-02-13

360 reads