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!
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 Blueifelse(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!
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!
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)
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