1 of 1 people found this helpful
Ok I have the solution you need ( I HOPE ;D )
1) create a calculated field (I called Sales2)
IF SUM([Sales])=0 THEN Null ELSE SUM([Sales]) END
2) Put in Row
3) Edit Format and set the two fields highlighted as the screenshot below
As you can see is a Measure yet and should not have issue in future related to this
can anyone help me out?
Here's a data set I mocked up in Excel:
Here's a view in Tableau with the 0 for Row ID formatted as . when used as a measure in a Measure Names/Values crosstab. You can build this manually by dragging in Measure Names & Measure Values, or when a single measure pill is on Text then double-clicking on Measure Values and then adding additional measures to the Measure Values card.
Here's the default number formatting for the Value (formatted) measure:
Note the 2nd semicolon and . at the end, this is the number format for 0.
One other note is the difference between using the SUM(Value (formatted)) as a continuous pill in Measure Values on Text vs. SUM(Value (formatted)) as a discrete pill on rows is that the Null value is a blank/empty value as the pill on Text and shows "Null" as the value when used as a discrete on Rows. This is Tableau's default behavior. If you are using the discrete pill on Rows presently then there are two typical workarounds: 1) switch to using Text or a Measure Names/Values crosstab or 2) write a calculated field that returns a string. In the latter case you'll have to deal with decimal places in the string formatting and potentially rounding problems because Tableau does not have the equivalent of a FORMAT() function, some people who have live connections to SQL sources will end up using a RAWSQL() function to get the proper number formatting.
I've attached a v10.5 packaged workbook. If this solution is not meeting your needs then I strongly suggest your next comment in this thread include a packaged workbook with sample data and view(s) that match what you are trying to do. I note that this thread has gone on for 17 responses with back-and-forth where various volunteers are making suggestions and they are not meeting your needs...a packaged workbook lets us see exactly what you are trying to do and make the best use of volunteers' time and yours in getting to a workable solution for you. For more information please see Packaged workbooks: when, why, how.
formatting zero as period.twbx 51.9 KB