Displaying cross sectional data in one visual

[Petri]

This is a conceptual question on how to best organize the data to get one visual used by several tables. In other words, aggregating data compactly in one place instead of showing 20 different tables.

In the usual textbook example, we have Sales table, Product Lookup Table and Calendar Table. Sales table has rows, with one row telling you the product, country, sales amount etc. What if instead you have 20 different Product Data Tables, one for each product, with sales information? Here is the first Product Table as an example:

Product Category = A

ProductName	Category1	Category2	QuantitySold	Date
Car	4 doors	Blue	5	12.12.2022
Car	4 doors	Black	3	10.12.2022
Car	2 doors	Red	1	11.12.2022

Next product table comes from different source, so the column names differ:

Product Category = B

ProductName	Level1	Level2	UnitsSold	Date
Teddy	Brown	Small	124	10.12.2022
Doll	White	Small	100	11.12.2022
Doll	Black	Medium	85	11.12.2022

The idea is that the users selects the Product Category (A or B) and date. Depending on that, the matrix visual shows the following generic headers and any data below these for the chosen Product Category:

Product

Cat1

Cat2

Sales

I know how to do this in Power Query and Excel: you have a lookup table that tells the column names for each Product Category. Then you branch the code depending on the Product Category and one table can handle all the output. It's a bit tedious. Also, the original tables could have the columns in different order or some other columns so appending the queries is also tedious, so I was wondering if Power BI offers an easier way to aggregate cross sectional tables.

I tried first to make all the column names the same but this does not help because you would need to drag all of them to Rows in the visual anyway.

Second attempt was to create a lookup table:

ProductCategory	Cat1	Cat2
A	Category1	Category2
B	Level1	Level2
C	Level1	Level2

and link it to the corresponding Product Table (e.g. Cat1 links to Product Table A[Category 1]) but this gives an error ("one of the columns must have unique values").

Another business case would be that a company has 100 factories, each submitting their production reports as separate tables. Management does not want to see 100 visuals (or 100 bookmarked sheets) but just one matrix that shows the key figures depending on which factory they choose.

What would be the best way to achieve this?

[Petri]

Thanks a lot for your effort! I ran in the following problems. First is that I cannot open the file:

I only have Power BI Desktop in use.

Also, when I create in my environment the Product and Category tables, I can link the Product Categories only in one-to-many (or many-to-one). Category Table I could not link at all to ProductA and ProductB tables ("one of the columns must have unique values"). Not sure if the issue is that there is a time dimension, so naturally Level1 and Level2 are repeated over time, just like "4 Doors" appears on two dates in the original post.)

[v-yanjiang-msft]

Hi @Petri ,

According to your description, here's my solution.

1.For CategoryA, CategoryB table, select Category1 and Category2(or Level1 and Level2) columns at the same time and click Unpivot. Then the category name in one column and the values in another column.

2.Create two tables.

Category table:

Product table:

Then make relationship between tables like this:

3.Create a measure:

Measure =
SWITCH (
    SELECTEDVALUE ( 'Category'[ProductCategory] ),
    "A",
        MAXX (
            FILTER ( 'ProductA', 'ProductA'[ProductName] = MAX ( 'Product'[ProductName] ) ),
            'ProductA'[Value]
        ),
    "B",
        MAXX (
            FILTER ( 'ProductB', 'ProductB'[ProductName] = MAX ( 'Product'[ProductName] ) ),
            'ProductB'[Value]
        )
)

Put ProductName from Product table in Rows, Category from Category table in Columns and the measure in Values.

Get the result:

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.