1 2 Previous Next 15 Replies Latest reply on Feb 1, 2019 7:59 AM by Barb Reiser

Is there a way to shade this last row only, KPI Weighted Rank? This measure is like a "total" of the measures above it so I want it to stand out.

Barb,

You can create a calculation  axis = 1

Drag it on the Columns (no aggregation)

Edit the axis, set  FIXED  0 to 1

Change the Mark for Bar

Click on Label  and check Show mark labels

Put Measure Names on Color and Edit the colors for each Measures

Michel

Hi Michel,

I'm not able to open your workbook as your using a newer version. I'm on 10.5. Are you able to save it using that version?

Barb

Oops  sorry about that. Here you go...

1 of 1 people found this helpful

I would need the header colored too so the whole row matches. Is there a way to do that?

By default Tableau doesn't allow to dynamically format the Headers, but if you don't care putting some time you can try this hack.

First instead of using the Measure Names and Measure Values, we will put one axis for each measure on the Rows. Each of this axis will get its own set of shelf properties.

So by putting a measure values on the Text of an axis, and still using the Bar mark, we can independently color each measure.

And since we cannot use Measure Name and  use it in a logic statement (can't do if Measure Name = 'something' then) we will trick Tableau to insert the name in the calculation and use the Total to display the name.

So first go in  Analysis - Total  -  Show Rows Grand Total  and  Row Total to left.

Now the goal here is for each measure  to  show the  Measure name in the Total  instead of the value.

We can control what we put in the total using the first() and last() functions.  first() will equal last()  only for the total.

So we gona apply the logic  if first() = last() then return the measure name  else return the measure value.

And because the name is a String then we need to Stringify the value of the measure also.

So you will need to make a copy of each measure like this.

if first() = last() then 'KPI Weighted Rank' else

STR( ROUND( [Rank CX]*.1+ [Rank MAPE]*.1+ [Rank CF Delta]*.1+ [Rank GR Delta]*.1+ [Rank CF BAR]*.08+ [Rank GR BAR]*.08+ [Rank Investigation]*.1+ [Rank BG Rate]*.16+ [Rank CF Enroll]*.09 + [Rank GR Enroll]*.09,2  )  )

end

Next put each of those measure on the Label shelf of it's corresponding axis and color the one you want. For the others just set the Color at 0%.

I did it for two measure you can complete the others.

I also formatted the Grand Total  to remove the header, you can put some text if you want.

Thanks Michel. I have done something like this before using a dual axis. I like the fact that you don't have so many pills for your way and that you don't have that extra axis taking up space in the view. I think I will give this a go.

Hi Michel,

I'm trying to use your method in another workbook, I've attached an example, but I'm not able to format it correctly. Do you know how I would re-write the formula if the values are a percentage? I can't get the measure title to appear in front of the scores either.

if first() = last() then 'Make' ELSE

STR( ( ROUND( RANK(AVG([Make]),'asc'),2 ) ) )

end

Barb,

First you need to display the Total column

Next , you need to fix the axis

Right click axis and click Edit

And click Fixed and set start to 0 and end to 1, this way the bar will use the full height

Once it`s done you can adjust the size of the axis  and then remove the axis from the view. Right-click the green pill and unselect  Show Header

Next, you set the bar size to use the full width

And to remove the Grand Total label, right-click Grand Total  and set whatever you want

For you measure,  if you want to display only the AVG in % instead of the Rank , you can go with

if first() = last() then 'Show' ELSE

STR(ROUND(AVG(Show)*100,2 )) + '%'

end

You're a lifesaver! Thank you!

Hi Michel,

Do you know how I would solve for this? If I change my filter to show just one month, the values disappear and it shows the measure name twice. If I have at least 2 months in my view, it's fine. I will need to be able to display one month when needed.

vs

Barb

Barb,

This is because  first() = last()  not only for the total, but also for the cell, since you only have one.

What you can do is instead of using the normal Contact Date filter, you can create a table calculation for your filter like this.

LOOKUP( MIN( DATENAME('month', [Contact Date] ) + ' ' +  DATENAME('year', [Contact Date] ) ),0 )

This simply lookup the date with an index 0 , so it returns the date itself  , in the format  mmm yy.

But because it is a table calculation, it changes the order of operations. And all table calculations will now be computed  before the filter is applied.

This way  first() = last()  will only return true for the Total cell  , even if there is a single month in the view.

Michel

Thanks! You're a genius.

One last question, since we are using the grand total in the view to show the measure name, is there a way to show the avg of each row to mimic a grand total column?

This one is maybe to much of a stretch to have on a single sheet.

The only possibilities would be to concatenate the value with the label, but even then the avg would compute on all the months even if a single one is in the view,  side effect of having a

table calculation on the filter shelf.

But actually  a more simple solution would be to use  two sheets in a Dashboard, you would have less complicated calculations to do.

Use one sheet to display only the labels, and the other one only the numbers. This way you can use a simple filter and simple aggregation.

1 2 Previous Next