Reporting over mutlitple entities in one repport

[BenJB]

Hi there,

I am struggeling with the way i want/can report data i have. And i can use some help with the way to approach this. I believe i have two options;

1.  Create a supporting table joining the data from 9 tables into one table and after that show it in graphs.
2. Create a visual with for example a KPI visual for each separate table.

The Case

Based on a main table i have generated 9+2 tables with queries to gather specific data from the main table. The 2 tables contain details of products deliverd to late or running past date. 9 tables contain counters for a productcategory, product, year, quarter, number of products, avg time producing.

I want to show management a couple things so they can act based on the data

1. a dashboard showing a generic time producing for the diferent products in the last quarter.
2. an 'out of bounds' dashboard showing production delays and being able to click through to show specific products and lines.
3. a trendline over multiple quarters and years

productcategory	product	year	quarter	num products in RTA	avg prod time in RTA
category 1	product a	2023	q4	15	8,00
category 1	product b	2024	q2	50	3,29
category 2	product c	2023	q4	10	5,18
category 3	product d	2024	q1	23	2,00
category 3	product e	2024	q3	5	6,05
category 4	product f	2024	q1	156	157,00
category 4	product g	2024	q1	235	50,25

The 9 tables all have the same collums but the last two, those have a coded collumnname for that specific data.

For example:

products in RTA and avg prod time in RTA,

products out RTA and avg prod time out RTA,

products in RTO and avg prod time in RTO,

products out RTO and avg prod time out RTO,

etc.

Two big questions,

1. Is it smart to adjust the current tables into something easier to report on?
2. How on earth do i report the amount of data (4 categories, about total 60 products) in such way it doesnt cause a jumble of lines nobody can make sense of.

Thanks in advance for your time.

p.s i am europe based so the , in 20,34 is the decimal marker

[Sahir_Maharaj · Accepted solution]

Hello @BenJB,

To answer your questions:

1. Yes - Combining these tables into one main fact table will make reporting much smoother. This way, you’ll have a single, consistent source for all your data. This lets you create measures more easily without dealing with multiple cross-table joins.

2. I recommend starting with summary visuals like KPIs and simple charts to show key metrics by category. You can set up drill-throughs so that viewers can click on summaries to see specific details when needed.

I would also add trendlines and slicers to help explore specific areas without the dashboard getting cluttered. This way, your report stays clean but still offers the depth needed.

Let me know if you might need any further assistance.