How to get Sum of New Business (table 1) + Renewals (table 2) by Team

[massaker]

Hello Community,

First post because I'm pulling my hair out understanding this issue. I swear I've done this before but now it doesn't seem to work.

What I Have: Two Tables (new business, renewals), each table has a column named April with the financial results, each table has rows with team names (East Coast, West Coast)
What I Want: A visualization summing the results of the table by team. I want to click on the visual I have for new business, click East Coast, and see that team's performance in the new "Total" visualization and the "Renewal" visualization.)

How do I do this? Originally I tried =SUM(newbusiness[APRIL])+SUM(renewals[APRIL])

This results in the results of new business for each team plus the sum of all teams in renewal visual. That's not correct. I want East Coast (new) + East Coast (renewal).
On top of this, there are two teams that do not have renewals. So I need to navigate null values. For example: Market Expansion (new business) + [null = 0 for renewals]. When I tried summing the two, I got a section called "(Blank)" which I could tell was the sum of the teams with nulls.

[v-jiascu-msft]

Hi, @massaker

As Mfelix said, creating a team table is useful. When you create team table and set the relationship, you can get the visual you want. If you want to sum the two columns from two tables, maybe you can try this measure. (Add it to the visual)

If you can post a sample data or the visual you want, that would be a help to get a solution. 

Measure =
IF (
    ISBLANK ( SUM ( Renewals[April] ) ),
    "(blank)",
    SUM ( NewBusiness[April] ) + SUM ( Renewals[April] )
)

[massaker · Accepted solution]

I figured it out!
1. My relationships were not matching up. 

2. My renewal table didn't have the default summarization: sum on.

3. Because renewal did not have summarization, it was summing when I created a calculated measure.

Thank you!

[v-jiascu-msft]

Hi, @massaker

I am so glad you figured it out. Could please mark your answer as a solution? That would be a help to others. Thank you. 

[massaker]

Hey v-jiascu,

Here's some more details. The pictures below show the fields that I have. Each table already has the Team Column, and they all have relationships. The two charts below that are what I currently have. I want to make a chart that looks like the last chart, but the data is not cooperating. You'll notice I have many month columns, the purpose for that is so I can just add the next month's numbers to the total for a year-to-date view. The last chart I was able to make before with a sum formula, but I had to reformat the data and for some reason cannot recreate in my new report. When I try to sum the two tables, Power BI takes the sum of the entire April column for one table, but then adds that number to the rows of the other table. I just want rows + rows, not column + row. Thank you for any assistance.

[MFelix]

Hi @massaker,

Add add a table only with the teams names and make a relationship between the teams tables and the other 2 aftere that add the team table in your x-axis and you renewal / new businnes to your values no need to do a sum.

Regards,
MFelix

[massaker]

Hey Mfelix,

I replied to v-jiascu below with pictures. There's already a relationship between the tables. They are the same except one table has different values in the April column.

Take a look and let me know how to get that sum chart. Ideally it's one chart where I can add months as they happen to make a year-to-date chart.