The Granularity Trap

From the “Data but Make it Personal” series – Part 6

When Your Data Level is Sabotaging Your Analysis

 

If you’ve been following this blog series, you know it’s all about connecting data concepts with everyday life. But every now and then, there are topics that deserve a more technical, straight-to-the-point approach. This blog will take a small detour into the practical side of data work. Less life analogies, more hands-on tips… well, mostly.

Because here’s the thing: no matter how clean, colorful, and clever your dashboard looks, it can still betray you if you overlook one simple but critical question: what’s the level of your data?

And when it’s off? Oh, you’ll know. Numbers won’t add up, stakeholders will raise eyebrows, and you’ll find yourself triple-checking formulas that were never the problem in the first place.

This is the story of one of the most common, least-talked-about issues in dashboard building, report writing, and general data analysis: granularity mismatches. It’s sneaky. It’s subtle. And it can ruin your logic before you even realise what’s gone wrong.

So, let’s talk about granularity and how it can lead to misleading information and head-scratching dashboards.

 

So, What Is Granularity Anyway?

Granularity is just a fancy way of saying: what’s the level of detail in your data?

Is it one row per:

• Transaction?
• Customer?
• Day?
• Store?
• Country?

The “grain” is the unit of analysis for your dataset, and you need to know it before you can start any meaningful work. If you don’t, you’ll end up joining apples to oranges and wondering why the numbers don’t make sense.

 

Why It Happens So Often

Granularity mismatches can happen because:

• Modern tools make it deceptively easy to join datasets without thinking.
• Stakeholders often don’t know (or care) about the grain.
• Different departments store data at different levels: daily sales, monthly budgets, quarterly targets.
• Tight deadlines make people skip the sanity check.

It’s no wonder this is one of the most common pitfalls in data projects.

 

A (Not-So-Hypothetical) Scenario

Let’s say you’re working on a sales report. You’ve got two datasets:

• One has daily sales by store.
• The other has monthly targets by region.

Your stakeholder wants a nice clean dashboard showing how stores are tracking against their monthly targets. Easy, right?

So, you join the two tables and start calculating variances. But when you add things up, the targets look suspiciously high. Like, “are we suddenly Amazon and no one told us?” high. You go through what I like to call the three stages of doubt (kind of like the stages of grief, but for data analysis):

1. Double-check the math.
2. Retrace your steps and
3. Wonder if the data’s wrong or if you’ve suddenly gone crazy.

But the real problem? You fell into the granularity trap.

Your sales data is at a daily-store level. Your target data is at a monthly-region level. By joining them without adjusting the granularity you essentially duplicated the target multiple times, once for every day and every store that matched the region.

It’s like trying to compare your monthly grocery budget to how many apples you ate each day — without accounting for the fact that your budget figure is being repeated 30 times. The math still runs, but the meaning is off.

 

You Know You’re in the Trap When…

 

• Totals seem way higher (or lower) than they should
• Percentages add up to more than 100%
• Stakeholders say: “That can’t be right…”
• You see the same value repeated again and again

 

So How Do You Avoid the Trap?

Here are a few ways to protect yourself (and your dashboard) from granularity chaos:

1. Understand Your Grain First: Before you start building calculated fields, ask: what is the grain of this dataset? Do the same for every table you’re using. If they don’t match, take a pause.
2. Use Relationships (Not Joins) When Possible: Tableau and Power BI let you define relationships between tables instead of physical joins. This keeps the data at its original grain and avoids unintentional duplication.
3. If You MUST Join, Aggregate Before Joining. If your fact table is more granular than your lookup table (like daily vs. monthly), aggregate it first. In Tableau you can use LODs to control this. In Power BI, SUMMARIZE or GROUPBY are your friends.
4. Test Aggregated Totals. Add a summary card to your dashboard with a known total. If something looks off, it probably is. This tiny step can save hours of troubleshooting.

 

What If You Need Different Granularities?

You don’t always have the luxury of perfectly aligned datasets. In real-world projects, you’ll often need to work across multiple levels of detail. That’s okay. The key is to:

• Keep each dataset at its correct grain.
• Use LODs or aggregation measures to bridge the gap.
• Be intentional about when and how granularity shifts.

Done well, this lets you create dashboards that show the big picture and the details without inflating metrics or muddying the insights.

 

Closing Lines

The granularity trap is frustrating because it doesn’t always scream “error.” Your visuals might load; your numbers might calculate but the insight underneath is flawed. It’s like writing a beautifully formatted email… to the wrong person.

So next time something doesn’t add up, don’t assume the problem is the formula. Zoom out. Check the grain. Because sometimes, the key to fixing your analysis is simply finding the right level.

After all, just like in life, it’s not just about what you know but also about at what level you’re looking.