Calculate the sum of table 2 based on table 1, display the summaries of both tables in the same matrix

[JeremiahMason]

I have two tables that are of the same design but contain different values. Essentially, I just want to combine two arrays in a /show result of the summaries of each table into a single visual. The following example shows the visuals I have and what I'm trying to produce, rather than the data itself, that is, the arrays shown below are a summary of my much larger data.

have:

Table 1 Matrix 1

category	Dollars
B	10
C	10
D	15
E	20

Table 2 Matrix 2

category	Dollars
B	5
C	15
D	10
E	0

I want to:

A matrix showing both summaries in a single visual

category	T1 Dollars	T2 Dollars
B	10	5
C	10	15
D	15	10
E	20	0

I tried using DAX to create a variable in table 1 that sums table 2 dollars based on my categories in table 1, but this failed.

Table2_Dollars de cálculo( SUM( 'Table2'[Dollars], FILTER( 'Table2', 'Table2'[Category] ? CALCULATE( VALUES( 'TABLE1'[Category]))))

Thanks in advance!

Update: Making a relationship between my two category variables worked for a single visual, but I want to repeat this several times for different variables that don't work because PBI doesn't let me have multiple active many-to-many relationships.

[v-lid-msft]

Hi @JeremiahMason ,

How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?

Best regards,

[JeremiahMason]

It's always right after you ask the question that you find the answer:

I created a many-to-many relationship between my category variables between my two tables and then simply adding the dollars field from the second table to the matrix I made with the first worked exactly as intended

[v-lid-msft]

Hi @JeremiahMason ,

We can try to make a dim table using following calculated table formula:

CategoryTable =
DISTINCT (
    UNION ( DISTINCT ( 'Table 1'[Category] ), DISTINCT ( 'Table 2'[Category] ) )
)

Then we can create 1:M relationship between dim table and other tables, use the Category table as the axis of chart to meet your requirement:

If you have any other questions, please kindly ask here and we will try to resolve it.

Best regards,