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.

[kcantor]

Create a measure that adds them together then sort by your sales rep on the rows.

Total Points = [New Customer Points Per Dollar]  + [New Customer Pts] + [Bonus Points]

Your measures are coming from different tables but should still total up in the new measure. Measures are portable that way.

[heathernicole]

That's exactly what I went for first. 🙂 

Here's what I end up getting: 

Full Error Message:

A single value for column 'New Customer Pts' in table 'Customer' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

So then I tried a calculated column - get the same error. I'm not sure what it means exactly...

[Sean]

@heathernicole

It seems you are adding 2 Measures and a Column! You can't do that.

Add a SUM(...) before the column name 

Edit: Or create a measure that SUMs that column and substitute it in the formula

Edit2: And verify you are getting the right answers since you are using multiple tables (just noticed)

if the filters propagate correctly based on the relationships you've set?

[heathernicole]

Here's what I get without adding the bonus points...

Then if I add the bonus points it splits it for some reason... 

I'm not sure where to go from here on this one.

In reality it should just put a numeric value against the New Customer Pts per Dollar column... if it's over 10,000 - put 2,000, if it's not - it should leave it alone... not split everything. 

[kcantor]

It looks like you are missing a relationship. You may need a bridge table. Can you provide us with a screenshot of your datamodel diagram?

[heathernicole]

@kcantor I can try... it's awfully big...

@kcantor and @Sean I got this far though. 🙂 So now it's behaving properly in the spreadsheet. Now I just need to to give a total of points for each Rep... 

[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

[heathernicole]

@Sean and @kcantor 🙂

Got it! Almost anyhow... it's really close. The spreadsheet is doing something funky with the totals row and column... here's a shot... Why is adding 2000? It's SO CLOSE to being right 😞  The problem is even in a graph it's adding that extra 2000. Any ideas or thoughts??? 

RE: Relationships... I can't get them on all in one shot - the tables are too big. 😞 But here's a go at it...

Here's the graph total for one rep...

[Sean]

@heathernicole I got it!

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

[heathernicole]

@Sean @kcantor  -

😞 

[kcantor]

The column name is different. It says 'Column' in it. Is the column titled that way? Isn't this the formula to create the column?

[Sean]

@heathernicole the only thing you have to do is

Create a Bonus Points Column with the same code as the Measure for Bonus Points

then substitute the Bonus Poins (measure) with SUM(of that Column) in your TOTAL

the TOTAL that sums the 3 columns

[heathernicole]

As a column.... it's not calculating the bonus points at all now. 😞 

It calculates it if the Bonus Points is a calculated measure instead of a column... go figure... 

[kcantor]

@heathernicole

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

[Sean]

@heathernicole use the exact same formula as in the top formula but in Bonus Points COLUMN

[heathernicole]

@Sean @kcantor 

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

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

[heathernicole]

@kcantor  This looks great!! 🙂 I will give it a go this afternoon! 🙂 

Thanks so much for all of your help!! 🙂 Will post the final outcome later today or Monday. @Sean