Hi,
I am using a table calculation (% of total) on top of a calculated field to determine the size of my circles on a worksheet. Depending on which function I filter, the size legend sometimes ends up with two decimal points (i.e., 5.00%) and sometimes ends up with three decimal points (i.e., 5.000%). I tried to set the default number formatting using the measures shelf but to no avail. I also tried to reproduce the problem using the Superstore dataset but could not reproduce. Unfortunately, I can't share the workbook due to confidential data.
Does anyone know how to set the number formatting of the size legend so that it has a fixed number of decimals?
For reference:
Here's the calculated field I am using called "Normalized Record Count" - it is designed to normalize the data given that I received more responses for some divisions than others and I want all divisions to count equally.
IF [Division]="Div1" Then [Number of Records]/2
ELSEIF [Division]="Div2 Then [Number of Records]/16
ELSEIF [Division]="Div3" Then [Number of Records]/16
ELSEIF [Division]="Div4" Then [Number of Records]/3
ELSEIF [Division]="Div5" Then [Number of Records]/6
ELSEIF [Division]="Div6" Then [Number of Records]/5
ELSEIF [Division]="Div7" Then [Number of Records]/2
ELSEIF [Division]="Div8" Then [Number of Records]/5
ELSEIF [Division]="Div9" Then [Number of Records]/16
ELSEIF [Division]="Div10" Then [Number of Records]/5
ELSEIF [Division]="Div11" Then [Number of Records]/11
ELSEIF [Division]="Div12" Then [Number of Records]/8
ELSEIF [Division]="Div13" Then [Number of Records]/15
ELSE [Number of Records] END
Here's the table calculation for % of total on "Normalized Record Count":
SUM([Normalized Record Count]) / TOTAL(SUM([Normalized Record Count]))
If you use the default number format and set it to number(custom) and to 1 decimal place that should work. If it still changes on you, you could try wrapping your formula in a round function like this
round(SUM([Normalized Record Count]) / TOTAL(SUM([Normalized Record Count])),1) to force the issue.
See if that helps
Jason