cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JeremiahMason
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
B10
C10
D15
E20

Table 2 Matrix 2

Category

Dollars
B5
C15
D10
E0

I want to:

An array that shows both summaries in a single visual

Category

T1 DollarsT2 Dollars
B105
C1015
D1510
E200

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]))))

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

3 REPLIES 3
v-lid-msft
Community Support
Community Support

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,

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.
JeremiahMason
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

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,

 

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.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors