Answered by:
Using measure at a certain hierarchy level and summarizing calculated results
Question

Hi,
I have a fourlevel 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
 Edited by Heidi EnhoMVP Wednesday, January 7, 2015 4:34 AM
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 Michael Amadi Wednesday, January 7, 2015 3:42 PM Additional detail
 Marked as answer by Heidi EnhoMVP 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: @nimblelearnWednesday, 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
 Edited by Heidi EnhoMVP Wednesday, January 7, 2015 2:32 PM
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 Michael Amadi Wednesday, January 7, 2015 3:42 PM Additional detail
 Marked as answer by Heidi EnhoMVP 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: @nimblelearnWednesday, January 7, 2015 11:11 PM