Issue with calculated field for percentage in tableau

0
votes

We have a report with 3 charts and 5 filters.

The 3 charts are :

Pie chart : based on cluster name

Machine count, which is the distinct count of all serial numbers.

Percent : which calculates the percentage of machines in pie chart/ machine count(machine count will remain fixed for cluster name)

In this dashboard the cluster name filter is not applied on the machine count worksheet. Rest all filters are applied on all the worksheets in the dashboard.

we have a calculation which is calculating the percentage of all the clusters to the total machine count. The formulas used are 

numerator : countd(if [cluster name] <> "Not Clustered" 
then 
[Serial No]
END)

denominator

countd(
[Serial No]
)

percent

round(([numerator]/[denominator]) * 100,2)

Screenshot :

Tableau Image

So if you refer to the above screenshot, when the filters value is changed for cluster name(say test cluster1) then percentage should be 4/21 = 19% but we are getting percentage as 100 %

So expected value is 19% but we aer getting 100 % How do we make sure that the machine count should be fixed and calculation is 4/21= 19% in this case.

Workbook attached at this link * Note : We tried to implement the above using LoD but did not get expected results.

1
tableau-api

1 Answers

0
votes

You may use below formula for denominator (we should be fixing the calculation in order to not be effected by our filters):

{fixed:SUM(([Number of Records]))}

And below for Nominator: (Computed automatically and summarized Table(Accross) ) 

TOTAL(countd(if [cluster name] <> "Not Clustered" 
then [Serial No] END))

And you percent field s/b:

[numerator]/SUM([denominator])

This way, you should be getting the correct percentage values. If not, I may send back the changed .TWBX file.