# Using measure at a certain hierarchy level and summarizing calculated results

• ### Question

• Hi,

I have a four-level hierarchy structure (Category, SubCategory, SubSubCategory and Item) in a PivotTable containing two measures (measure 1, measure2). I need a third measure that compares results of those two measures at SubSubCategory level to choose the maximum of those two measures. Selection should not be done at Item level or SubCategory or Category level.

Item level results should give blank values as result. After choosing the bigger measure value at SubSubCategory level, measure 3 should calculate the sum of measure 3 at SubCategory level and of course at Category level.

BR,

Heidi

Wednesday, January 7, 2015 4:31 AM

### Answers

• Hi Heidi,

Thanks for providing the additional information. If I've understood your requirement correctly then the following DAX formula should give the desired result:

```Measure 3:=IF(
NOT ISFILTERED('Data'[Item]),
SUMX(
ADDCOLUMNS(
VALUES('Data'[SubSubCategory]),
"Greater Measure", IF([Measure 1] >= [Measure 2], [Measure 1], [Measure 2])
),
[Greater Measure]
)
)```

The formula is always comparing the measures at the SubSubCategory granularity and picking the highest measure value for each SubSubCategory. The result is then rolled up to higher levels i.e. Sub Category and Category. The outermost IF function has been used to stop any result from being returned for the Item level in Pivot Tables.

This is the output that I got after dragging it onto the Pivot Table:

Edit: I noticed that the language setting that you are using means your DAX parameter delimiter is ';'. Simply replace all of the ',' in the formula I provide with ';' and it should work for you :)

Regards,

Michael Amadi

Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

Website: http://www.nimblelearn.com, Twitter: @nimblelearn

• Edited by Wednesday, January 7, 2015 3:42 PM Additional detail
• Marked as answer by Wednesday, January 7, 2015 4:11 PM
Wednesday, January 7, 2015 3:32 PM

### All replies

• Hi Heidi,

Could you kindly provide an example and some test data to further clarify what you're after. Also, it would be helpful to know the DAX formulas for the 2 measures.

Regards,

Michael Amadi

Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

Website: http://www.nimblelearn.com, Twitter: @nimblelearn

Wednesday, January 7, 2015 9:33 AM
• Sorry, this might help.

Measure 3 (bigger one) is:

=IF (
ISFILTERED (data[SubSubCategory]) && NOT (ISFILTERED (data[Item]));
IF([Measure1]>=[Measure2];  [Measure1];  [Measure2]);
BLANK()
)

Best Regards,

Heidi

Wednesday, January 7, 2015 2:31 PM
• Hi Heidi,

Thanks for providing the additional information. If I've understood your requirement correctly then the following DAX formula should give the desired result:

```Measure 3:=IF(
NOT ISFILTERED('Data'[Item]),
SUMX(
ADDCOLUMNS(
VALUES('Data'[SubSubCategory]),
"Greater Measure", IF([Measure 1] >= [Measure 2], [Measure 1], [Measure 2])
),
[Greater Measure]
)
)```

The formula is always comparing the measures at the SubSubCategory granularity and picking the highest measure value for each SubSubCategory. The result is then rolled up to higher levels i.e. Sub Category and Category. The outermost IF function has been used to stop any result from being returned for the Item level in Pivot Tables.

This is the output that I got after dragging it onto the Pivot Table:

Edit: I noticed that the language setting that you are using means your DAX parameter delimiter is ';'. Simply replace all of the ',' in the formula I provide with ';' and it should work for you :)

Regards,

Michael Amadi

Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

Website: http://www.nimblelearn.com, Twitter: @nimblelearn

• Edited by Wednesday, January 7, 2015 3:42 PM Additional detail
• Marked as answer by Wednesday, January 7, 2015 4:11 PM
Wednesday, January 7, 2015 3:32 PM
• Thanks a lot! It worked well.
Wednesday, January 7, 2015 4:18 PM
• Glad that it helped :)

Regards,

Michael Amadi

Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

Website: http://www.nimblelearn.com, Twitter: @nimblelearn

Wednesday, January 7, 2015 11:11 PM