locked
dont display toplevel total in powerpivot RRS feed

  • Question

  • Hi

    I have  products rolling up to Product type e.g.

    Product type is top level in hierarchy, I dont want to display aggregated value at productype level... (130 should not be displayed) when user connects to powerpivot.

    How can i achieve this

    thanks

    Monday, April 21, 2014 3:21 PM

Answers

  • Hi Ricky,

    As far as I know you will have to create a measure that checks that all levels below productType are not filtered and ProductType itself is filtered (to avoid removing the total value for all product types).

    It would look like this whereas depending on the number of Levels below product type you would have to add more ANDS:

    =if(and(NOT(ISFILTERED([TableColumnNameInHierarchy));ISFILTERED(ProductType[ProductID]));BLANK();[YourMeasure])

    The concept is described here:

    http://www.sqlbi.com/articles/clever-hierarchy-handling-in-dax

    Regards,

    Julian

    Monday, April 21, 2014 9:40 PM
  • In addition to what Julian already said, you basically have to check if all lower levels are NOT FILTERED

    doing a check using ISFILTERED() on the highest level will always return true once you drilled down on that level

    -gerhard


    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com

    Tuesday, April 22, 2014 12:08 PM
    Answerer

All replies

  • Hi Ricky,

    As far as I know you will have to create a measure that checks that all levels below productType are not filtered and ProductType itself is filtered (to avoid removing the total value for all product types).

    It would look like this whereas depending on the number of Levels below product type you would have to add more ANDS:

    =if(and(NOT(ISFILTERED([TableColumnNameInHierarchy));ISFILTERED(ProductType[ProductID]));BLANK();[YourMeasure])

    The concept is described here:

    http://www.sqlbi.com/articles/clever-hierarchy-handling-in-dax

    Regards,

    Julian

    Monday, April 21, 2014 9:40 PM
  • In addition to what Julian already said, you basically have to check if all lower levels are NOT FILTERED

    doing a check using ISFILTERED() on the highest level will always return true once you drilled down on that level

    -gerhard


    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com

    Tuesday, April 22, 2014 12:08 PM
    Answerer