Tableau possible IF LOD calculation

Question

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

Colorado                  Furniture                $13,243

                          Office Supplies          $7,899

                          Technology               $10,966

Answer 1

• 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.