Forum Discussion
Sum multiple columns from multiple tables? How to?
heathernicole just add the 3 Measures into a new one (or 2 Measures and 1 SUM(Column))
EDIT: as to kcantor point about the Relationship View - we just need to see the tables involved in this calculation and the columns connecting them
Speaking to the extra 2000: Unlike in excel, PowerPivot and PowerBI calculate each 'cell's' individual total based upon the calculations. Each cell is an island so to speak. While it may only show up once in your visual, within the calculation itself it must appear more than one time. There is a reference card that helps with this issue and it can be found here:
http://www.powerpivotpro.com/2015/10/giving-back-steal-this-reference-card/
As for your data model . . . wow. Do you need all of those tables for this particular use? I only ask because a coworker of mine tried to use one data model for everything and it slowed it down to a crawl. A smaller, more nimble datamodel can quickly be created for many projects. She had about 10 fact tables and no lookup tables.
How many actual tables are you using from this diagram for this partiular project and have you dropped any columns you don't need to reduce size?
In general, I have 4 lookup tables that I match to most fact tables. My largest model uses 5 fact tables but I keep the column count as low as possible to reduce size and calculation time. Also, are you populating your rows from a lookup table or from a fact table? Using a fact table as row values can cause many issues and play Mary Hobb with your numbers.
heathernicole Obiviously your Bonus Points Measure is the Problem...
In the bottom total row it defaults to the value between 25000 and 49999 because thats the total in the bottom row
Therefore you need to convert it to a Calculated Column instead of a Measure (use same formula)
Then create a Measure that will sum that Column and then add all 3...
Looking at the screen captures it appears that the grand total is recalculating the bonus points total based upon the grand totals of the other columns. You can either swich to a calculated column or look at using a much longer workaround.
this will also affect subtotals.
Edit: Looks like Sean beat me to that one. :)
last ditch effort here:
Go back to the original measure that worked correctly in the row but not in the grand total. Apply the 'fix' from this blog post to correct how the total is calculating.
http://www.powerpivotpro.com/2012/03/subtotals-and-grand-totals-that-add-up-correctly/
The problem is only the grand total as it meets the other critera for raising points. If you correct it to calculate differenlty it should solve the issue.
heathernicole I created this sample data to hopefully resemble what you are working with
The 3 Tables are related this way
Try the formula highlighted in the image below
It worked for me! I hope it works for you too! Let me know...
I am out of time for today but this may be the solution you need. check out this blog post. I had it marked to revist to fix some grand total issues my other reports had experienced. It may help here if you go back to the measure formula and add this quantifier so that it doesn't add extra points. It has to do with telling the calculation engine that if the calculation is pulling for multiple 'representatives' to leave this blank. I will check back here tomorrow and see if you have this solved. I will also continue to ponder this . . .
last ditch effort here:
Go back to the original measure that worked correctly in the row but not in the grand total. Apply the 'fix' from this blog post to correct how the total is calculating.
http://www.powerpivotpro.com/2012/03/subtotals-and-grand-totals-that-add-up-correctly/
The problem is only the grand total as it meets the other critera for raising points. If you correct it to calculate differenlty it should solve the issue.
heathernicole I created this sample data to hopefully resemble what you are working with
The 3 Tables are related this way
Try the formula highlighted in the image below
It worked for me! I hope it works for you too! Let me know...
heathernicole Many Errors get resolved buy simple Restart of your system.
I'm assuming you are running 64-bit PBI and have enough memory.
I get a similar error but in PowerPivot working on the same exact data set that never crashes in PBI.
Restart solves it for me but then again this may be something different - wouldn't hurt to try if you haven't already...
The other thing is you may think about importing just the tables you need for you reports/dashboards.
How big is that pbix file you are working with and how many tables does it have - do you need them all?
- Good morning! :)
Just to follow up -
I did go back and point to the original column (with the same name) this morning.
- When the Bonus Points is a Column instead of a Measure - it doesn't calculate at all. It doesn't throw an error, but it won't work either.
- When I left the Bonus Points as a column and tried to make the Pts Per Dollar as a column - I get a circular dependency error.
Not sure what else to do at this point... :/
Okay keep us posted... I'm sure you'll figure it out
Well - I'm kind of stuck in this rut... Sean
- If I make Bonus Points a column - and leave Pts Per Dollar as a measure - it doesn't calculate properly.
- If I make Pts Per Dollar and Bonus Points each a column... it creates a circular dependency
- In order to what you, kcantor suggested... http://www.powerpivotpro.com/2012/03/subtotals-and-grand-totals-that-add-up-correctly/ ... it requires a column... and won't work with a measure.
So I'm stuck in this vicious cycle now. :)
Not sure what to do. To me.. this seems like an odd behavior. I'm not sure what to do at this point.
heathernicole You have 3 tables involved and I don't know how they are connected
The picture you posted of the Relationship view didn't show these 3 tables together and how they are related
Hey Sean! :) Thanks so much! I'm trying it out now - I keep getting a memory error message, 'the operation has been cancelled because there is not enough memory..."
You've got the setup pretty close, except that "Customer" is not referenced directly in the Sales Details table. There's a link for Customer ID and it uses the Customer table as lookup table for additional information.
Everything else looks about right. Still working through it with the things you've tried. Will keep you posted.
THANKS!!!!
