How to color code % changes by row

[Anonymous]

Hi guys,

I have question regarding the color coding technique for a pivot table I created.

 In the image below, for a given row, I want the color to change to 'green' if the % is higher in the next month or to 'Red' if it is lower than previous month.

For example,

when FICO Bin is '<650', the color of (3%) will be green and also 'green' for the month of 'June'.

If % in June would have been lower than 3%, it should be 'Red'.

If I can add an arrow ( up or down), it would be even better.

 

Thanks in advance!!

[jdbuchanan71]

You can write a measure to use the new conditional formatting in tables and matrix.

FormatMeasure = 
VAR PriorMonth = CALCULATE([YOUR MEASURE], DATEADD('Date'[Date],-1,MONTH) )
VAR CurrentMonth = [YOUR MEASURE]
RETURN IF ( CurrentMonth < PriorMonth, "ColoredArrowDown", "ColoredArrowUp")

Replace [YOUR MEASURE] with whatever the measure name is you are showing in the matrix.

Then apply icon conditional formatting to your table using the new measure.

I fond the icon names in this blog post.

https://powerbiuniversity.com/actualizacion-julio-fin-los-iconos-formato-condicional-la-creacion-kpi...

Name list is at the bottom.

[Anonymous]

Thank you for sharing the solution. But, currently, I am using the PBIRS (May 2019 version) which does not have the 'Icon formatting' feature.

Is there any other way I can use?

[jdbuchanan71]

No, the icon feature is only available in the newest version.  Why not just updated PowerBI descktop?  If you can't then you can change the measure to return the font color "Green" or "Red" and apply the conditional formatting to the value.

[Anonymous · Accepted solution]

Unfortunately, we are only supposed to use the On-premise PBIRS only so I cannot upgrade to the latest Power Bi version. But, I was able to work it out in a different way.

I created two measures:

1. % Approved

2. Prior Month Approved = CALCULATE([% Approved], DATEADD(dCalender[DimDateKey],-1,MONTH))

Then I created a thrid measure and added it to the matrix:

Arrows = SWITCH( TRUE(),

ISBLANK([Prior Month Approved])

|| ISBLANK([% Approved]),BLANK(),

[% Approved] <= 0, UNICHAR (11166),

[% Approved] >= [Prior Month Approved],UNICHAR(9650),

[% Approved] <= [Prior Month Approved],UNICHAR(128315),

BLANK()

)

where, UNICHAR(9650) is an 'Up Arrow' and UNICHAR(128315) is a 'Red Down Arrow'.

Thanks for the quick responses. I really appreciate it.