locked
Using measure at a certain hierarchy level and summarizing calculated results RRS feed

  • 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 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: @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 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: @nimblelearn

    Wednesday, January 7, 2015 11:11 PM