Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
hoggwildd
Regular Visitor

Two problems I thought would be easy.

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

1 ACCEPTED SOLUTION

For the denominator, can you use:

 

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

 

 



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

7 REPLIES 7
Greg_Deckler
Super User
Super User

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.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

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_idSalesRepName1Status DateJobStatusGrossAmount
WPASmith, Joe6/27/16Bank Reject$         10,628.00
WPASmith, Joe6/24/16Complete & Paid$           9,900.00
WPASmith, Joe6/15/16Bank Reject$         11,876.00
WPASmith, Joe6/7/16Complete & Paid$           8,658.64
WPASmith, Joe6/1/16Complete & Paid$         13,523.74
WPASmith, Joe5/24/16Cancel In Resc$         11,457.00
WPASmith, Joe5/24/16Cancel In Resc$         10,684.00
WPASmith, Joe5/23/16Cancel off Backlog$         28,607.00
WPASmith, Joe5/21/16Complete & Paid$         12,820.66
WPASmith, Joe5/17/16Complete & Paid$           5,338.00
WPASmith, Joe5/14/16Complete & Paid$         11,570.93
WPASmith, Joe5/11/16Complete & Paid$         11,236.89
WPASmith, Joe4/26/16Cancel In Resc$         13,274.00
WPASmith, Joe4/25/16Complete & Paid$         11,148.24
WPASmith, Joe4/23/16Complete & Paid$         10,644.78
WPASmith, Joe4/22/16Complete & Paid$         10,700.00
WPASmith, Joe4/20/16Complete & Paid$         12,172.36

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.

 

salesrepoffices.png

 

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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

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

For the denominator, can you use:

 

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

 

 



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

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

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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.