cancel
Showing results for
Did you mean:
Regular Visitor

## Calculate the sum of table 2 on the basis of table 1, show the summaries of both tables in the same matrix

I have two tables that are of the same layout but contain different values. Essentially, I just want to combine two arrays into a /show result from 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:

An array that shows 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 to use DAX to create a variable in table 1 that sums table 2 dollars based on my table 1 categories, but this failed.

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

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 won't let me have multiple many-to-many relationships active.

3 REPLIES 3
Community Support

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Regular Visitor

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

Community Support

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,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements