locked
Is it possible to create a pivot table report based on Power Pivot that has different resolution for different values? RRS feed

  • Question

  • Please see the screen shot below.

    The I value (inventory) is the same for all sales companies, so for every item I only want it to be listed once.

    The S value (sales) is different for each sales company, so for every item I want it to be listed per sales company.

    Is this possible?

    Monday, October 12, 2015 11:03 AM

Answers

  • To my knowledge there is no way in a pivot table to create the behavior that you want.

    You could create a dummy sales company value for evaluating your inventory measure. This way you could force [I] to return BLANK for anything except for this dummy row. The measure would look something like this:

    ConditionalInventory:=
    IF(
        HASONEVALUE(DimSalesCompany[SalesCompany])
        ,IF(
            VALUES(DimSalesCompany[SalesCompany]) = "Dummy company"
            ,[I]
            ,BLANK()
        )
        ,BLANK()
    )

    A pivot table will not display rows for which a measure evaluates to BLANK, so this will force no display of all your [SalesCompany] values.

    GNet Group BI Consultant

    • Marked as answer by JP3O Tuesday, October 13, 2015 10:31 AM
    Monday, October 12, 2015 2:16 PM
  • You're referring to a different column in your HASONEVALUE() predicate and your VALUES() function.

    VALUES() returns a list of distinct values in context, either from a whole table (unique rows across all fields) or a single column. We cannot compare a column with a scalar, "TOTAL". When we try to, we see the error you're getting.

    Thus, we need to guard the evaluation of the predicate VALUES(dimSalesCompanies[SalesCompany]) = "TOTAL" to only occur when we know that the left side has just one value.

    This is why the external IF()'s HASONEVALUE() must refer to the same field as the internal IF()'s VALUES() (or you can use a pair of fields that map 1:1 in all contexts, e.g. a description field for a numeric code).

    GNet Group BI Consultant

    • Marked as answer by Charlie Liao Wednesday, October 14, 2015 6:30 AM
    Monday, October 12, 2015 3:21 PM

All replies

  • To my knowledge there is no way in a pivot table to create the behavior that you want.

    You could create a dummy sales company value for evaluating your inventory measure. This way you could force [I] to return BLANK for anything except for this dummy row. The measure would look something like this:

    ConditionalInventory:=
    IF(
        HASONEVALUE(DimSalesCompany[SalesCompany])
        ,IF(
            VALUES(DimSalesCompany[SalesCompany]) = "Dummy company"
            ,[I]
            ,BLANK()
        )
        ,BLANK()
    )

    A pivot table will not display rows for which a measure evaluates to BLANK, so this will force no display of all your [SalesCompany] values.

    GNet Group BI Consultant

    • Marked as answer by JP3O Tuesday, October 13, 2015 10:31 AM
    Monday, October 12, 2015 2:16 PM
  • I like your idea for a work-around, but I get the error message:

    "MdxScript(Model) (9,9) Calculation error in measure 'calcPSI'[P No Repat]: A table of multiple values was supplied where a single value was expected"

    I tried using MAX() and VALUE() instead of VALUES(), but those didn't work either.

    P No Repeat:=IF(
        HASONEVALUE(parPISalesCompany[Sales Company])
        ;IF(
            VALUES(dimSalesCompanies[SalesCompany]) = "TOTAL"
            ;[P]
            ;BLANK()
        )
        ;BLANK()
    )

    Monday, October 12, 2015 2:42 PM
  • You're referring to a different column in your HASONEVALUE() predicate and your VALUES() function.

    VALUES() returns a list of distinct values in context, either from a whole table (unique rows across all fields) or a single column. We cannot compare a column with a scalar, "TOTAL". When we try to, we see the error you're getting.

    Thus, we need to guard the evaluation of the predicate VALUES(dimSalesCompanies[SalesCompany]) = "TOTAL" to only occur when we know that the left side has just one value.

    This is why the external IF()'s HASONEVALUE() must refer to the same field as the internal IF()'s VALUES() (or you can use a pair of fields that map 1:1 in all contexts, e.g. a description field for a numeric code).

    GNet Group BI Consultant

    • Marked as answer by Charlie Liao Wednesday, October 14, 2015 6:30 AM
    Monday, October 12, 2015 3:21 PM
  • Perfect, thank you!
    Tuesday, October 13, 2015 10:45 AM