2 views
in BI

This one seems simple but I'm stumped. Hoping someone has come across this one before.

I want to show:

State Category Sales\$ if Furniture is the highest Sales\$ category for that State

Ex: Arizona Furniture \$13,525

However, if Furniture is not the top Sales \$ Category I want to show 0 or NULL or "Red Flag". Any could work, "Red Flag" would be best but I'm not sure we can couple an 'ag' with a string.

Ex: Alabama Furniture "Red Flag"

Thanks for any suggestions that get this right!

State                     Category                 Sales

Alabama                   Furniture                \$6,332

Office Supplies          \$4,209

Technology               \$8,969

Arizona                   Furniture                \$13,525

Office Supplies          \$10,006

Technology               \$11,751

Arkansas                  Furniture                \$3,188

Office Supplies          \$4,565

Technology               \$3,925

California                Furniture                \$156,065

Office Supplies          \$142,352

Technology               \$159,271

Office Supplies          \$7,899

Technology               \$10,966

by (17.6k points)
• Let's go step by step. First let's create a [max_sales] field:

{ FIXED [State] : MAX([Sales]) }

• This will give you the max sales of each state, among the categories. Now, lets assume that's the lowest level of detail (category). If it is not, you can use another LOD calculation to have the figures for each category:

{ FIXED [Category] : SUM([Sales]) }

And use this field on the first calculation.

• Now for the next part. You can't mix integers with strings, but you can always convert int to str. I would do:

IF [Category] = 'Furniture' THEN IF [Sales] < [max_sales] THEN 'Red Flag' ELSE STR([Sales]) END ELSE #I have no idea what you want here END

• Again, if category is no the lowest level of detail, use SUM([Sales]) instead of just sales (and MAX([max_sales]) if Tableau says you can't mix aggregate with non-aggregated fields)

You can enroll in this Best Tableau Course online by Intellipaat to learn basic and advanced Tableau.