Color to Pivot Table Row

[Syndicate_Admin]

Dear:

I have a doubt about the conditional colors, in a pivot table, but these do not go to the result but to the rows, I need all the companies in China, to chew another color.

In the link is the example

https://drive.google.com/drive/folders/1jQMdqyy0sCaX9h8pVbFYDgyQj6jm6GXf?usp=sharing

Thanks in advance for the reply

[jdbuchanan71 · Accepted solution]

You can create a formatting measure like this.

Formatting = IF ( RIGHT ( SELECTEDVALUE ( Tabla1[Empresa Pais] ), 5 ) = "China", 1)

Then apply a conditional format to the values when the measure = 1

No, I don't know of a way to apply the color to the row headers.

[jdbuchanan71 · Accepted solution]

The only way I can think to do that would be to add a ranking measure that ranks all the '- China' rows then all the rest of the rows.  The problem is then you have to add the ranking measure to the matrix and collapse all the Rank columns so you can use it to sort but hide it in the result. 

I have attached my updated copy of your file for you to look at.

Rank = 
VAR _ChinaRows =
    COUNTROWS (
        CALCULATETABLE (
            DISTINCT ( Tabla1[Empresa Pais] ),
            RIGHT ( Tabla1[Empresa Pais], 5 ) = "China",
            ALLSELECTED ( Tabla1[Empresa Pais] )
        )
    )
VAR _ChinaRow =
    RIGHT ( SELECTEDVALUE ( Tabla1[Empresa Pais] ), 5 ) = "China"
RETURN
    IF (
        HASONEVALUE ( Tabla1[Fecha] ),
        BLANK (),
        IF (
            _ChinaRow,
            RANKX (
                FILTER (
                    ALL ( Tabla1[Empresa Pais] ),
                    RIGHT ( Tabla1[Empresa Pais], 5 ) = "China"
                ),
                CALCULATE ( SUM ( Tabla1[Venta] ), REMOVEFILTERS ( Tabla1[Fecha] ) )
            ),
            RANKX (
                ALL ( Tabla1[Empresa Pais] ),
                CALCULATE ( SUM ( Tabla1[Venta] ), REMOVEFILTERS ( Tabla1[Fecha] ) )
            ) + _ChinaRows
        )
    )

[jdbuchanan71]

@jmerdman 

You can use a measure like this to set the conditional formatting color for the values background.

Formatting = 
VAR _Bucket = SELECTEDVALUE ('Aging Buckets'[Aging Bucket])
RETURN
SWITCH(
    _Bucket,
    "Future Date","#ECECEC",
    "0 to 100 Days","#7FFF8C",
    "101 to 200 Days","#BFE352",
    "201 to 300 Days","#FADF66",
    "301 to 365 Days","#E3A252",
    "> 365 Days","#FB785A"
)

[jdbuchanan71 · Accepted solution]

The only way I can think to do that would be to add a ranking measure that ranks all the '- China' rows then all the rest of the rows.  The problem is then you have to add the ranking measure to the matrix and collapse all the Rank columns so you can use it to sort but hide it in the result. 

I have attached my updated copy of your file for you to look at.

Rank = 
VAR _ChinaRows =
    COUNTROWS (
        CALCULATETABLE (
            DISTINCT ( Tabla1[Empresa Pais] ),
            RIGHT ( Tabla1[Empresa Pais], 5 ) = "China",
            ALLSELECTED ( Tabla1[Empresa Pais] )
        )
    )
VAR _ChinaRow =
    RIGHT ( SELECTEDVALUE ( Tabla1[Empresa Pais] ), 5 ) = "China"
RETURN
    IF (
        HASONEVALUE ( Tabla1[Fecha] ),
        BLANK (),
        IF (
            _ChinaRow,
            RANKX (
                FILTER (
                    ALL ( Tabla1[Empresa Pais] ),
                    RIGHT ( Tabla1[Empresa Pais], 5 ) = "China"
                ),
                CALCULATE ( SUM ( Tabla1[Venta] ), REMOVEFILTERS ( Tabla1[Fecha] ) )
            ),
            RANKX (
                ALL ( Tabla1[Empresa Pais] ),
                CALCULATE ( SUM ( Tabla1[Venta] ), REMOVEFILTERS ( Tabla1[Fecha] ) )
            ) + _ChinaRows
        )
    )

[Syndicate_Admin]

Thank you very much for the prompt and accurate response.

Best regards

[Syndicate_Admin · Accepted solution]

Thank you very much for the prompt and accurate response.

Best regards

[jdbuchanan71 · Accepted solution]

You can create a formatting measure like this.

Formatting = IF ( RIGHT ( SELECTEDVALUE ( Tabla1[Empresa Pais] ), 5 ) = "China", 1)

Then apply a conditional format to the values when the measure = 1

No, I don't know of a way to apply the color to the row headers.

[jmerdman]

I could use some assistance on something similiar, but I need to each row a different color. How could you add to your Formatting statement to include different values. In the screen shot below I need to color code the row by each Aging Bucket. I was able to to do 1 row but need to do them all. 

[Syndicate_Admin]

An additional query, there is a way that what is marked in pink, all are in the top positions of the list?, that is, the first 5 would be the pink ...

[Syndicate_Admin]

Thank you very much, it is exactly what I needed, I accept it as a solucuón.

Best regards