I have written the calculation below. It get's me 90% of the way to where I need to be, but without the other 10% it's worthless. Hoping someone can help me with this one:
IF sum([header sum])>=1000000 then sum([header sum])/1000000
ELSEIF sum([header sum])>=1000 AND sum([header sum])<999999 then sum([header sum])/1000
ELSE IF sum([header sum])<1000 then sum([header sum])
END
END
The calc evaluates the 'header sum' value and converts it to trillions, billions, or millions as appropriate. I need to tack on the appropriate text (in red):
IF sum([header sum])>=1000000 then sum([header sum])/1000000 + 'trillion'
ELSEIF sum([header sum])>=1000 AND sum([header sum])<999999 then sum([header sum])/1000 + 'billion'
ELSE IF sum([header sum])<1000 then sum([header sum]) + 'million'
I suspect the answer may involve wrapping things in STR(), but I haven't been able to make it work.
Apologies for not posting a packaged workbook. It's proprietary and trying to scrub it was taking forever.
Hi Doug
I have replicated a basic version in superstore
I'm hoping you can copy the methodology and apply it to your situation...
This is now valid in Tableau...
IF sum([Sales])>=1000000 then STR(sum([Sales])/1000000) + ' trillion'
ELSEIF sum([Sales])>=1000 AND sum([Sales])<999999 then STR(sum([Sales])/1000) + ' billion'
ELSEIF sum([Sales])<1000 then STR(sum([Sales])) + ' million' ELSE NULL END
Cheers
Mark