Forum Discussion
Two problems I thought would be easy.
For the denominator, can you use:
Denominator = ABS((TODAY() - DATE(2016,11,30)) / 30)
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.
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 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.
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
