I'm creating a PBI Matrix that compares the monthly budget and actual sales quantities and amounts, with 3 levels of products to drill down (Category > Product Group > Item). These are in different tables, I think I had linked them all, but the results are not showing correctly.
These are what each table contains:
1. 'Items' table from Business Central: the list of all items, used Data Groups to create a column called [Product Group], and used Conditional Column to create a column called [Category]. then created a hierarchy: Category > Product Group > Item
2. 'Sales' table from Excel: the actual sales quantities, it contains [Document No.], [Quantity] and [Item No.] - this 'Sales' [Item No.] has a many-to-one relationship with 'Items' [Item No.]
3. 'General Ledger' table from Business Central: the actual sales dates and amounts, used Conditional Column to create columns [Product] and [Category], a hierarchy is also created - 'General Ledger' [Document No.] has a many-to-many relationship with 'Sales' [Document No.], but many-to-many relationship can't be created between 'General Ledger' [Product] and 'Items' [Product Group]
4. 'Budget' table from Excel: budget sales quantities and amounts for each product (not the detailed items) - this 'Budget' [Product] has a one-to-many relationship with 'Items' [Product Group]
I've tried a few combinations, but the results I got only show either the correct budget figures or the correct actual figures, but not both on the same Matrix. I thought the problem was on the link between the Data Group and a column, so I tried creating a separate table to link them, but the result was the same.
I want to try changing the many-to-many relationships to one-to-many, but I can't think of how.
There can be multiple items in each invoice, so 'Sales' [Document No.] is not a distinct list. 'General Ledger' [Document No.] is not a distinct list either.
And how to build a relationship between the conditional column 'General Ledger' [Product] and 'Items' [Product Group]?
Thanks
