Highlight and total based on value

Hi Azakir

This is a great opportunity to use the new OffSet function in PowerBI! Previously, this would have been quite hard to achieve. As it is not officially release, there is no documentation or intellisense, but I have been using it for sometime so worked out a way to do it.

First, you need to create one field as a sort order. EG 

EmpAndDateSort = Sheet1[Employee] & Sheet1[Date]

 

And then a measure for the role - as I can only get the function to work this way. This expression is a hack to return the current role as text:

RoleMeasure = FIRSTNONBLANK(Sheet1[Role],0)

 

The offset function can then give you an expression which returns the "previous rows value" as if it was ordered like a table

 

OffsetCalc = Calculate([RoleMeasure], OFFSET(-1,ALLEXCEPT(Sheet1,Sheet1[Employee]  ), orderby(Sheet1[EmpAndDateSort],asc )))

 

"All except" in this instance tells DAX to make a line at the employee dimension - otherwise the offset value would be continuous between employees (which we dont want)

 

Visualised, this looks like (with my dummy data)

 

You can see now it is easy to make a comparison on the "current" rows role, and the offset role. this can then be used to return a value that can be used in conditional formatting for the cell.

 

This measure looks like  

Comparison Calc = if( and([OffsetCalc]<>[RoleMeasure],not isblank([OffsetCalc])),1,0).

Note I had to exclude non blank values as these are represent the "first" row of each employee's data

 

Once you have these elements, the cell backgrounds in a pivot table can be edited under the chart heading -> cell elements -> background colour

 

Click "fx" and use this comparison measure to set the colour

 

This is the result:

 

 

Hope this helps

 

Pi