How to alternate dimension's colour on pivot tables

    After a unsuccessful search in the community I reached a workaround that maybe will be useful for somebody.

     

    I've worked only on graphic expressions without any additional field calculated in the script so the layout is dynamic avoiding the risk of displaying adjacent values highlighted by the same colour after a selection or a manual sorting change.

     

    I used an auxiliary expression labeled aux to delineate with 1 and 0 the alternance of dimension's values:

     

    if(Dim1<>Above(TOTAL Dim1), if(above(TOTAL aux)=0, 1, 0), above(TOTAL aux))

     

    Then I made a condition in background expression of all columns I wanted to format:

     

    if([aux] = 1, rgb(198,239,206))

     

    (aux could be replaced with Column(n) function, where n is the number of the expression containing aux definition)

     

    This is the result using 2 auxiliary fields to colour alternately 2 different dimensions:

     

    FormattedPivot.png

    The drawback of this solution is that in pivot tables it's not possible to hide auxiliary fields.

    If your final goal is to export the table in Excel or pivot visualization is not your first priority, you could transform the chart in a straight table hiding aux and aux2 as below:


    straightFormatted.jpg

    Check attachment for a clearer explanation.

     

    MR