Two problems I thought would be easy.

[hoggwildd]

Hello everyone,

As a self taught person with the book PowerPivot and Power BI along with what I can find online I get myself confused, so thanks for any help. I am trying to do 2 things:

1. In a table I have a list of sales reps in one column and what office they work for in another. On my pivot table I am trying to have them listed on two columns, this sounds easy (and probably is) but it is driving me nuts. I can add the offices UNDER their names but when I try to add the office as a column is lists all of the offices (total of 😎 stretching out the table. Each rep works only in one office, so I want  that office listed for that rep, and only that office. Currently I get all 8 and then blank cells with their sales volume for the offices they dont work in. I have tried writing a measure for the offices, but that does not seem to work, I can get the "Count of Offices" as a column, but not the name.

2. The other thing I am trying to do is show how much a sales rep has to do per month to hit a target number. We end our sales year on 11/30 and I have written this measure:

CLUB YEAR = CALCULATE([Installed $],DATESBETWEEN('Date'[Date],"12/1/2015","11/30/2016"))

where Installed $ = CALCULATE(Jobs[Sum of GrossAmount],Jobs[JobStatus]="Complete & In house"||Jobs[JobStatus]="Complete & Paid"||Jobs[JobStatus]="Complete & Unpaid"||Jobs[JobStatus]="Complete/Await Insp"||Jobs[JobStatus]="Warranty transfer"||Jobs[JobStatus]="Manufacture War Only"||Jobs[JobStatus]="Legal")

This works perfectly to give me the running total so far year to date. I am trying to figure how much they would have to average from TODAY (whatever date I am looking at the data) until 11/30 to hit $1,000,000 in sales, average by month.

FOR EXAMPLE: If a rep's total club year as of TODAY, 8/29 is $750,000, then there is 3.1 months left until the end of the sales year, so the math would be (1,000,000 - 750,000)/3.1= 80,645.  Then I look again on 9/1 and he has $760,000 and the math would be - (1,000,000- 760,000)/3 = 80,000. I can get the numerator to work ( I wrote a measure for that, but am open to suggestions), but I cannot for the life of me figure how to get the denomintor to calculate the time remaining in the club year.

Again, THANK YOU for taking the time to read and for any help on either problem,

HW

[Greg_Deckler]

Can you share some sample data or a representation of your data? I am thinking that you could CONCATENATE your sales rep name and office into a single field and solve the first one that way.

[hoggwildd]

Thank you for your reply.

I have used the concatenate before but that would not allow me to sort the table by either sales rep name or office, or filter out to only look at one office and how the sales reps in that office are doing (although i could use office as a filter I guess). If there is not another way to get the office as its own column then I will use that suggestion.

As for a sample of the data: (brn_id = office)

brn_id	SalesRepName1	Status Date	JobStatus	GrossAmount
WPA	Smith, Joe	6/27/16	Bank Reject	$         10,628.00
WPA	Smith, Joe	6/24/16	Complete & Paid	$           9,900.00
WPA	Smith, Joe	6/15/16	Bank Reject	$         11,876.00
WPA	Smith, Joe	6/7/16	Complete & Paid	$           8,658.64
WPA	Smith, Joe	6/1/16	Complete & Paid	$         13,523.74
WPA	Smith, Joe	5/24/16	Cancel In Resc	$         11,457.00
WPA	Smith, Joe	5/24/16	Cancel In Resc	$         10,684.00
WPA	Smith, Joe	5/23/16	Cancel off Backlog	$         28,607.00
WPA	Smith, Joe	5/21/16	Complete & Paid	$         12,820.66
WPA	Smith, Joe	5/17/16	Complete & Paid	$           5,338.00
WPA	Smith, Joe	5/14/16	Complete & Paid	$         11,570.93
WPA	Smith, Joe	5/11/16	Complete & Paid	$         11,236.89
WPA	Smith, Joe	4/26/16	Cancel In Resc	$         13,274.00
WPA	Smith, Joe	4/25/16	Complete & Paid	$         11,148.24
WPA	Smith, Joe	4/23/16	Complete & Paid	$         10,644.78
WPA	Smith, Joe	4/22/16	Complete & Paid	$         10,700.00
WPA	Smith, Joe	4/20/16	Complete & Paid	$         12,172.36

[Greg_Deckler]

Thanks, data makes it so much easier!

Here is what I get with your data and a simpel Table visualization in Desktop. I added a line for myself in the WPB office.

This looks OK to me but I assume I am doing it one way and you another since you mention pivot tables.

[hoggwildd]

That can get it done when I publish in Power BI, is there a way to get it to work in Power Pivot?

Any ideas how to write a measure that will show the amount needed each month to reach the goal?

Again, thank you very much for your time.

HW

[Greg_Deckler · Accepted solution]

For the denominator, can you use:

Denominator = ABS((TODAY() - DATE(2016,11,30)) / 30) 

[hoggwildd]

WHOO HOO!

THANK YOU, THANK YOU, THANK YOU!

Never read or found anything about "ABS", obviously still have a LOT to learn.

Have a wonderful day,

HW

[Greg_Deckler]

Let me look at the other issue.

For Power Pivot, right click the pivot table and go to PowerTable Options, click the Display tab, click the checkbox for "Classic PivotTable layout..."?