Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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!
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] )
)
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!
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.
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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHey 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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
98 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |