Sum multiple columns from multiple tables? How to?

[heathernicole]

This is the last part of a large custom report.

I've created two measures and a caluculated column:

1. New Customer Points Per Dollar = CALCULATE(ROUNDUP( SUM('SALES DETAILS'[Sales Line Sales Amount]),0), ALLEXCEPT('Sales Rep', 'Sales Rep'[Sales Rep Name]))  (measure)

2. New Customer Pts = IF(Customer[Age of Customer (in days)] <= 365,4000,0)

3. Bonus Points =
IF('SALES DETAILS'[New Customer Points Per Dollar] >= 10000 && 'SALES DETAILS'[New Customer Points Per Dollar] <= 24999,
2000,
IF('SALES DETAILS'[New Customer Points Per Dollar] >= 25000 && 'SALES DETAILS'[New Customer Points Per Dollar] <= 49999,
4000, IF('SALES DETAILS'[New Customer Points Per Dollar] >= 50000,
6000,
0)
))

(measure)

These columns are based of Customer Name Column, Sales Rep Name, and Age of Customer Column.

I am trying to sum the top three columns Grouped by Sales Rep Name and Customer Name.

The Bonus Points one seems to be causing issues. I only want to add from that column if there is a value > 0. Otherwise ignore it. 

EXAMPLE:

BOB(rep)            New Customer Pts           New Customer Pts per Dollar                  Bonus Points

Customer 1              4000                                  150                                                      0

Customer 2              4000                                   0                                                         0

Customer 3              4000                                   400                                                     2000

Total                         12000                                 550                                                     2000

Ultimately I need the total pts for Bob:

Total = 12000 + 750 + 2000 = 14,750 pts

You can have new customer pts, but no Pts per dollar yet or no bonus pts but Pts per dollar and New Customer Pts. 

ANY ideas or help would be greatly appreciated!! 🙂 Thanks so much in advance!

[Sean · Accepted solution]

@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

[kcantor · Accepted solution]

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.

[Sean · Accepted solution]

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

[kcantor · Accepted solution]

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

[kcantor · Accepted solution]

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.

[Sean]

Okay keep us posted... I'm sure you'll figure it out

[heathernicole]

Well - I'm kind of stuck in this rut... @Sean

1. If I make Bonus Points a column - and leave Pts Per Dollar as a measure - it doesn't calculate properly.
2. If I make Pts Per Dollar and Bonus Points each a column... it creates a circular dependency
3. 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. 

[Sean]

@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

[Sean · Accepted solution]

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

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!!!! 

[Sean]

@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?

[heathernicole]

@Sean @kcantor - First of all- THANK YOU SO MUCH for your help. I'm have to leave for the day also - I went back and corrected everything - changing ONLY the Bonus Points to a Bonus Points Column (Same Name).

Here's the code: 

Bonus Points =
IF('SALES DETAILS'[New Customer Points Per Dollar MEASURE] >= 10000 && 'SALES DETAILS'[New Customer Points Per Dollar MEASURE] <= 24999,
2000,
IF('SALES DETAILS'[New Customer Points Per Dollar MEASURE] >= 25000 && 'SALES DETAILS'[New Customer Points Per Dollar MEASURE] <= 49999,
4000, IF('SALES DETAILS'[New Customer Points Per Dollar MEASURE] >= 50000,
6000,
0)
))

Sum of Contest Points = (SUM('Customer'[New Customer Pts])) + [New Customer Points Per Dollar MEASURE] + (SUM('SALES DETAILS'[Bonus Points]))

New Customer Points Per Dollar MEASURE = CALCULATE(ROUNDUP( SUM('SALES DETAILS'[Sales Line Sales Amount]),0), ALLEXCEPT('Sales Rep', 'Sales Rep'[Sales Rep Name])) 

The problem - is as a column - Bonus Points doesn't calculate the 2000 at all... as a measure it does.

So now my report is MINUS 2000 instead of plus 2000.

I'll be able to reconvene in the morning. Thanks so much for helping me!!! Will check back in later! Have a good night. 🙂 

[Sean]

@heathernicole use the exact same formulas as the measure

[heathernicole]

@Sean I did  

[Sean]

EDIT:

[Sean]

@heathernicole You should be able to make it work now.

[heathernicole]

@Sean - those numbers don't look quite right...

The Bonus Points column isn't totaling correctly... But the sales Amount and Points Per Dollar Measure aren't.

In the Totals Points Due Column in Result 2 ... that's not adding properly either.

thoughts??? 

@kcantor - just to throw a little more of a twist... it doesn't do it here. 🙂 

[heathernicole]

@Sean What did you do differently?? My code (unless I missed something) looks the same???

@kcantor I did drop the totals in the design matrix... but it still keeps it there... ??? As if it's still adding it?

[kcantor · Accepted solution]

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]

@kcantor - thanks for the resource! I haven't had a chane to delve into yet- there's a lot there.

In short - does that mean that there isn't a way to correct that behavior?

It didn't do it in this chart: ???

RE: Relationships. MOST of those tables are lookup tables. The main one is the Sales Details table. Pretty much all of the others are lookups. In this particular model I use the Sales Details, Sales rep, Customer, and Calendar - Transaction table. 

My goal is to trim down the extra tables next. I'm kind of nervous because I don't know what that will do. So I'm going to make a copy of this report and then try it on a dummy first. It will be too heady to work with at that size after awhile. 

That extra two thousand points may be a bit of an issue... I need to see if there's a way to keep it from doing that. I'll look into that resource you sent. THANKS!! 🙂 

[kcantor]

It didn't do it in the chart because you do not have a grand total in the chart. You can go into the matrix design and drop the grand total for a quick fix but I would be more interested in why it is adding it twice in the grand total.

The resource I sent you is a reference card. The first page explains how to step through a measure to determine where that measure is breaking down. It seems to be working everywhere other than at the grand total which makes it difficult to track the issue. If we were working in PowerPivot, i would suggest manually filtering your data model by that particular sales rep to see where the extra points are coming from but, as I am still relatively new to PowerBI as well, I am not sure how to go about doing that within PowerBI.

Of course, now that I know that I don't know how to do that, I will be learning how to do it as soon as possible. If I make a discovery pretty quickly, I will let you know.

[heathernicole]

Neither of those is working properly... 😞 

Just to sum that one column doesn't work. It seems to be the Bonus Points that messes up the equation when it comes to filtering. 

The relationships are there. This seems like basic math... you'd think this was relatively simple... 😞