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