Hi All,

I spent a bit of time trying to figure this one out, but unfortunately I could not come up with a solution as i do not believe i have the expertise to solve this one.

The Problem:

Currently, I am looking to calculate the % yearly total of Offices [(Shrt Nm)] not submitting files ([0 Files Check]) for the cluster they are associated with [(Geo Cluster)]. If an Office has not submitted a file for a specific month it is flagged as 1, while if it has, it is flagged as 0.

The Catch:

My data is structured in a way that does not seem to be cooperating with tableau on this matter. For each Office, we have a flag to determine whether or not documents were filed per month. So for example, for a complete year, Office A will have 12 months associated with it and for each month there will be a 1 or 0 tagged with it to specify if files were filed or not. Whenever I attempt to do represent this data on a yearly level, it does not work out....I am looking to have each unique office be assigned a 1 or 0 to determine if it had files submitted that year or not, and than use that total to calculate the total % that did not submit. I tried using LoD calculations, but that did not seem to work for me.

Tableau Example of issue:

The expected results should be 3 and 100 if we look want to look at the yearly picture....but Tableau is aggregating each monthly line based on the formulas i used.

I have attached a sample workbook and data set to demonstrate the issue I am having, the expected results would be as follows:

*edited expected results

Thanks for any insight!

Kind regards,

Anthony