Hello Sameeksha - Create a Calculated Field called KPI and use the formula below:
if [C]<([A]+[B])/3 THEN "Red"
elseif [C]>([A]+[B])/3 THEN "Yellow"
elseif [C]>([A]+[B]) THEN "Green"
Drag this field in the Color Marks and set the color accordingly.
I am not able to fill the cell with the color after dragging it to color marks. Can you please help me out with it?
I thought of highlight tables. I want "name" to be highlighted as well.
If this solve your problem, then please mark this as correct answer and close the thread.
Can you attach the packaged workbook.
I want to see your data structure as well.
The data is confidential. So, I can't share the data. Refer to the excel snapshot that I want to replicate in tableau. If you need any further clarifications, let me know.
I think the solution will depend in part on how your data is structure. If the data is structured like this:
Name Category Value Alpha A1 (value) Alpha B1 (value) Alpha C1 (value) Alpha A2 (value) Alpha B2 (value) Alpha C2 (value) etc
the solution could be different than if the data is structured like this:
Name Period A B C Alpha 1 (value) (value) (value) Alpha 2 (value) (value) (value) Alpha 3 (value) (value) (value)
With the first data structure, I was able to get fairly close to your example using table calculations and a highlight table:
This solution also works if you break category into two fields, for example a Category (A, B, C) and a time (1, 2, 3). The key is that you have a single measure per row rather than 3 separate measures per row. The only thing I wasn't able to replicate was shading the NAME field based on the formatting of the C columns. I don't believe that Tableau allows you to format/shade the levels of dimension in the header separately. As a work around I added a "status" column to the beginning, assuming that the purpose of the color coding was to draw attention to those that needed attention. (I added the 4th NAME to have an example where all fields were green.) This solution does involve using table calculations to lookup the values in various columns, so it does require C to be every 3rd column. I've attached it as a packaged workbook in v9.3 (since that's the only version I have on this computer).
If your data is structured in a different format, then a different solution will be required.
Thread 204076.twbx 29.5 KB
The data can't be restructured that way. The data is not dependent. It is just a nomenclature that I followed. Have you modified the data that way to create a view like the one you showed here?
I am not able to open the attachment. So, can you please comment the code you wrote under table calculation?
To sort the categories, I used the following calculation to rearrange the numbers and letters into a new dimension:
I then used the drop down for [Category] and sorted it by the dimension I just created. This allowed things to be sorted A1, B1, C1, A2, B2, C2, etc.
To format the "C" columns, I used this formula:
[Format Table Calculation]
if min(left([Category],1))="C" then
if sum([Value]) >= zn(lookup(sum([Value]),-2)) + zn(lookup(sum([Value]),-1)) then "1-Green"
elseif sum([Value]) >= ( zn(lookup(sum([Value]),-2)) + zn(lookup(sum([Value]),-1)) / 3) then "2-Yellow"
elseif sum([Value]) < ( zn(lookup(sum([Value]),-2)) + zn(lookup(sum([Value]),-1)) / 3) then "3-Red"
This formula limits the calculations to the categories that start with "C". It then uses the LOOKUP() function to get the values of the previous 2 cells. You do need to make sure that it is computing along the [Category] dimension. I used this calculation with a highlight table to get set the colors of the different cells. The number was included at the beginning so I could use the MAX() function in the following formula to see the most severe:
lookup(left([Format Table Calculation],1),2),
lookup(left([Format Table Calculation],1),5),
lookup(left([Format Table Calculation],1),8)
when "1" then "GOOD"
when "2" then "CAUTION"
when "3" then "PROBLEM"
Unfortunately this formula and approach only works with a fixed number of columns to lookup the values. It might be possible to create a calculation that determines the max across the row regardless of the number of columns using a table calculation.
Hope this helps