many to many relationship problem

Hello all, beginner at excel and first time posting here. After a lot of pain.

I have a sales table with data from the past few years, and wanted to be able to analyze sales across different categories- specifically 'product type' (an arbitrary category for analysis) and 'scent'.

Now the thing is, some products might fit into more than one product category, so simply adding a column wasnt going to cut it.

After discussing this with my excel advisors (chatgpt and claude..) I ended up doing the following:

A table called "Products" with 1 column: Product ID (unique)

A table called "Tags" with 1 column: Tags (unique)- this includes both the product types as well as scents.

A table called Product_tags - 2 columns: ID, and tag. Theyre both not unique (since a each ID likely has at least 2 tags, for type and scent, and sometimes 3).

Finally, the Sales table, which has a bunch of sales data, as well as a column for ID, and these are also repeating as well.

I created a data model (for the first time i think) and added all the tables. I then created the following relationships:

Sales <- Products (By ID) - many to one relationship

Product_tags <- Products (by ID) - many to one relationship

Product_tags <- Tags (by Tag) - many to one relationship

Now my problem:

When I create a pivot table (from data model) I can group or filter the revenue by product ID (the one from the products table) and it works. However, the tags dont work.

Whichever tag I choose (from the 2 tag tables) doesnt work for grouping or for filtering when I use it with any item from another table. for example, if I put "ID" from the product_tags table with "tag" from the same table, both as rows, I get to see each product, and which tags it has. or each tag and all the product IDs relevant to it.

However, if I put tags with "ID" the sales table or from the products table, the grouping doesnt work. Each tag has all products show up and vice versa. Basically, the tags dont work with the other data in my data model...
I cant seem to make this work, i've been at this for hours. This analysis is rather important for me to get done by today yet the day is basically over, and I havent been able to do anything.

Help me out.

Thanks.