Quality indicator /How to link two tables

[Karlie69]

Hello 🙂

I have two tables: a register of claimed products (complaint number, type, claimed qty, reason etc.) and table of all produced products (type, produced qty etc.).

First table (register of claimed products)

Complaint No.	Type	Claimed qty	Year	Quarter
20210307	wardrobes	7	2021	1
20210422	tables	30	2021	2
20210831	chairs	100	2021	3

Second table (all produced products)

Code	Type	Produced qty	Year	Quarter
WARD001	wardrobes	300	2021	1
TABL008	tables	758	2021	2
CHA005	chairs	2310	2021	3

Based on first table, I created a report with statistics and charts. However I'd like to add two other indicators:

• qty of claimed tables and chairs / qty of produced tables and chairs
• qty of claimed wardrobes / qty of produced wardrobes

I created three measures:

• Comp/Qty All types = FORMAT ( ( SUM(Register[Claimed qty]) / SUM(Overall[Produced qty]) ) , "Percent")
• Comp/Qty Wardrobe = CALCULATE(
  Quantity[Comp/Qty All types],
  FILTER(
  CROSSJOIN(
  ALL(Overall[Code]),
  ALL(Register[Type])
  ),
  Quantity[Code] = "WARD001" &&
  Register[Type] = "Wardrobes"
  )
  )

• Comp/Qty w/o wardrobes = CALCULATE(
  Quantity[Comp/Qty All types],
  FILTER(
  CROSSJOIN(
  ALL(Overall[Code),
  ALL(Register[Type])
  ),
  Quantity[Code] <> "WARD001" &&
  Register[Type] <> "Wardrobes"
  )
  )

There are the two filters in this report (year and quarter). Both tables are linked by the "Year" column. Unfortunately, this is exactly the problem. When I want to see the indicators for one year and all quarters - everything works great. But when I want to see the indicators for one year and one quarter - there is mistake. The indicator is calculated according to the formula: quantity of claimed products in a given quarter / quantity produced for all quarters. What can I do for the indicator to take into account the number of products produced only in a given quarter?

I thought about creating an index with which I would link both tables (e.g. year_quarter), but that would also be wrong. There are product complaints not in every quarter, so there would be no such data in the table.

Anyone have an idea to solve my problem? 🙂 I would like the indicator to be calculated correctly both for all quarters and for one quarter.

[TomMartens · Accepted solution]

Hey @Karlie69 ,

I recommend that adjust your table to this

create three dimension tables

• Product
• Claims
• Time

Then you can create relationships between these tables and the already existing ones, that are called fact tables.

You have to be aware that a relationship between the tables can be established on using a single column, for this reason you have to create a new column in the existing tables that are concatenating the content from the year/quarter columns.

Creating a data model is key for performance, analytical capability, and also data visualization. For this reason, I recommend this tutorial as a starter for data modeling with Power BI: Model data in Power BI - Learn | Microsoft Docs

Hopefully, this provides some new insights and will help to tackle your challenge.

Regards,

Tom