I'm working with Tableau to analyze a dataset of insured people, and I need to calculate the average number of distinct insured individuals ([Cod Asegurado]) across the 12 months of a year. I want to achieve this without using Level of Detail (LOD) expressions and without displaying the months in my visualization. Additionally, I need to be able to filter the data based on certain criteria, which is why LOD expressions are not suitable for my use case.
Context:
I have a dataset with records of insured individuals, including a unique identifier ([Cod Asegurado]) and a corresponding month (the first day of the month as a Date field). I want to calculate the average of ( distinct count of [Cod Asegurado] ) across the 12 months of the year. The Data is already filtered to display a given year only. The end goal is to have a population pyramid displaying the given averages across different age ranges for a given year.
The result should be a single number representing the average, and I should be able to apply filters to the data.
Desired Output:
A calculated field in Tableau that computes the average distinct count of [Cod Asegurado] across the 12 months of a year, without displaying the months and without using LOD expressions. The result should dynamically update based on any filters applied to the data.
What I've Tried:
Using WINDOW_AVG with COUNTD:
I created a calculated field with the formula: WINDOW_AVG(COUNTD([Cod Asegurado])). I configured the table calculation to compute using Table (Across) and set it to be fixed on the year.
Attempting to Use Table Calculations:
I tried various configurations of table calculations, including setting the calculation to be fixed on the year and using different partitioning options. None of these attempts yielded the correct average distinct count across the 12 months without displaying the months.
What Hasn't Worked:
The calculations either produced incorrect results or required displaying the months in the view, which is not desired.
Using LOD expressions is not an option because I need the calculation to be dynamic and responsive to filters applied to the data.
Question:
How can I create a calculated field in Tableau that computes the average distinct count of [Cod Asegurado] across the 12 months of a year, without displaying the months and without using LOD expressions?
I ended up creating an aggregate table in the ETL phase instead.