# How to parse out highest Orange level employee and filter all relevant data accordingly?

I am looking to see how I can use a series of calculated fields to parse out the highest rank employee (by the color orange). But at the same time I want to show the location name and a headcount measure of all employees distinguished by color for each department? I’ll leave a sample dataset below that contains dummy data, as well as a visual of the outcome I’m looking to achieve in my table. Much help from anyone who has a solution to this!

Hey @manbig. Hopefully you will hear some suggestions from the Community soon. Just in case, I have pinged one of our SAs to see if they can reply back on Monday! Hope you are having a good weekend!

Thank you for the update @Kristin hope to hear back soon on this topic!

1 Like

Hi @manbig, sorry for taking a couple of days to get a solution to you. Here’s what I did:

• Create a calculated field for Orange `ifelse(Color = 'Orange', 1, 0)`, and one for Blue `ifelse(Color = 'Blue', 1, 0)`. You should try to do these in data prep if possible.

• Create a calculated field called IsOrange `ifelse(Color = 'Orange', 1, 0)` (you need this to be used in the grouping, but you need your ‘Orange’ field to be a measure, so you do need two fields with the same calculation).

• Create a calculated field for Total `Orange + Blue`

• Create a calculated field called Display Rank `maxOver(ifelse(Color = 'Orange', {Employee Rank}, 0), [Department], PRE_FILTER)`. This sets non-Orange rank values to 0 and then gets the max, which will give you the highest Orange value

• Create a calculated field called Display Name `lastValue(Name, [IsOrange ASC, {Employee Rank} ASC], [Department])`

• Create a pivot table and add the following in this order to Rows: Department, Display Rank, Location, Employee Rank, IsOrange, Name. Set the last 3 of these to hidden. If you don’t include them you’ll get an error, but they can be hidden.

• Add Display Name, Orange, Blue, Total.

• Open the Format Visual pane for the pivot table, select Hide +/- buttons in Pivot Options.

• In Total, set Show totals, and change the Label to Grand Total. You can change the background if you want to.

• In Subtotal, set Show subtotals, change Level to Custom and select Department from the dropdown. Add background styling if you need.

• In Row names, change Display Rank to Employee Rank, and in Value names change Display Name to Name.

Hope that helps!

2 Likes

Thank you @Steph!

@manbig, if Steph’s solution works for you, we’d love it if you could please help out the community by marking it as solved (check box under her reply). Thanks!

1 Like

Hello @Steph and @Kristin thank you for getting back to me! I was working through the solution you’ve provided and was running into an error on Step 4. I’ll attach a screenshot of what the error message relays. Could I get some help on trying to fix this!

Hi @manbig, looks like you’re trying to set the Display rank to a string, and that your Employee Rank field is also as string. The maxOver field expects a measure or date, and so you would need that field to be an Integer. If you can’t change the Employee Rank field in the Data Preparation area (which I would recommend if you can), you can use parseInt in this function
`maxOver(ifelse(Color = 'Orange', parseInt({Employee Rank String}), 0), [Department], PRE_FILTER)`

2 Likes

Thanks for the clarification @Steph! Since I don’t have access to edit the original data source, adding the ‘parseInt’ function and transforming it to a measure field worked perfectly! You Rock

2 Likes