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

Solution

Let's go part by part. 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. I'm assuming 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 aggregated with non-aggregated fields)

Comments

[pop_princess]

Hi lnox! I applied your LOD above and it works in some, but not all. AZ: "red Flag' for furniture, but should be 'ok' for all categories since 'furniture' is highest sales. same with CO. Another added detail to include: not all states have a 'furniture' category. How does this change LOD calculation? If no 'furniture' category, then it should still result in 'ok'.

[Inox]

You have to work on the ELSE statement. ELSE STR([Sales]) should work fine, but I'm not sure what your data looks like. Try to understand what the calculation is doing, so you can adapt it

[pop_princess]

the IF statement should have been IF [Sales] >= [max_sales] THEN 'Red Flag'. When I did this, it worked but it's now listing all the 'furniture' sales amount vs. just saying 'ok' since I added the '=' to. How do I fix this?

[Inox]

Well, the STR([Sales]) prints the sales amount. If you want just an 'ok', replace it by 'ok'

[pop_princess]

I just applied above calculation into my actual workbook and forgot something. When applying { FIXED [State] : MAX([Sales]) }, I forgot that the [Sales] is from another workbook that was merged into the final report. The measure is named: SUM([WKLY_Sales_Details].[Sales] when I replaced the MAX([Sales]) with my version. The error that I got was: "Argument to MAX(an aggregate function) is already an aggregation, and cannot be further aggregated" Help....