Forum Discussion
Sales Vs Goal for multiple locations (data included)
Hello,
I have been hammering away at this data and would appreciate the help GREATLY! I am in short trying to compare Sales Data and Goal Data, side by side like the bar chart pictured below. I need to be able to flip back and between different locations using a slicer.
I have put together sample data, which I hope will provide further insight into this. I tried connecting a relationship between the dates to no avail. I receive an error each time.
Thank you so much in advance for the help.
"Location - Woo" "Shoot Date" "Total Cost"
| Wayne | 3/6/2018 | 1250 |
| Wayne | 3/6/2018 | 1250 |
| Wayne | 3/6/2018 | 1750 |
| Wayne | 3/7/2018 | 1750 |
| Wayne | 3/12/2018 | 1750 |
| Wayne | 4/10/2018 | 1250 |
| Wayne | 3/8/2018 | 1250 |
| Wayne | 3/23/2018 | 600 |
| Wayne | 3/26/2018 | 1250 |
| Wayne | 4/11/2018 | 1750 |
| Wayne | 3/24/2018 | 1750 |
| Wayne | 3/24/2018 | 1250 |
| Wayne | 3/24/2018 | 1250 |
| Wayne | 3/24/2018 | 1250 |
| Wayne | 3/24/2018 | 1250 |
| Wayne | 4/16/2018 | 1750 |
| Wayne | 4/20/2018 | 1800 |
| Grand Rapids | 3/3/2018 | 2000 |
| Grand Rapids | 3/5/2018 | 1500 |
| Grand Rapids | 3/6/2018 | 1500 |
| Grand Rapids | 3/6/2018 | 1350 |
| Grand Rapids | 3/5/2018 | 1250 |
| Grand Rapids | 3/8/2018 | 1500 |
| Grand Rapids | 3/6/2018 | 3000 |
| Grand Rapids | 3/5/2018 | 900 |
| Grand Rapids | 3/5/2018 | 750 |
| Grand Rapids | 3/8/2018 | 382.5 |
| Grand Rapids | 3/6/2018 | 1500 |
| Grand Rapids | 3/6/2018 | 700 |
| Grand Rapids | 3/12/2018 | 3000 |
| Grand Rapids | 3/12/2018 | 3000 |
| Grand Rapids | 3/7/2018 | 2000 |
| Grand Rapids | 3/9/2018 | 1500 |
| Grand Rapids | 3/6/2018 | 3250 |
| Grand Rapids | 3/9/2018 | 4500 |
| Grand Rapids | 3/6/2018 | 800 |
| Grand Rapids | 5/4/2018 | 1000 |
| Grand Rapids | 3/7/2018 | 700 |
| Grand Rapids | 3/7/2018 | 700 |
| Grand Rapids | 3/7/2018 | 1350 |
| Grand Rapids | 3/19/2018 | 4500 |
| Grand Rapids | 4/2/2018 | 1350 |
| Grand Rapids | 3/26/2018 | 700 |
| Grand Rapids | 3/27/2018 | 1350 |
| Grand Rapids | 3/23/2018 | 2000 |
| Grand Rapids | 3/27/2018 | 1500 |
| Grand Rapids | 3/24/2018 | 1000 |
| Grand Rapids | 3/26/2018 | 1000 |
| Grand Rapids | 3/28/2018 | 1000 |
| Grand Rapids | 4/17/2018 | 2700 |
| Grand Rapids | 4/18/2018 | 750 |
| Grand Rapids | 4/23/2018 | 4000 |
| Grand Rapids | 4/19/2018 | 1000 |
| Grand Rapids | 4/17/2018 | 2700 |
| Grand Rapids | 4/23/2018 | 1045 |
"Month" "Location" "2017 Historical" "2018 Plan" "Shoots Plan" "Model Revenue"
| 1/1/2018 | Wayne | 0 | 3925 | 24 | 3340 |
| 2/1/2018 | Wayne | 0 | 6829 | 41 | 5812 |
| 3/1/2018 | Wayne | 0 | 10361 | 62 | 8818 |
| 4/1/2018 | Wayne | 0 | 14521 | 87 | 12358 |
| 5/1/2018 | Wayne | 0 | 19309 | 116 | 16433 |
| 6/1/2018 | Wayne | 3395 | 25250 | 151 | 18036 |
| 7/1/2018 | Wayne | 4217 | 22912 | 137 | 16366 |
| 8/1/2018 | Wayne | 6126 | 29739 | 178 | 21242 |
| 9/1/2018 | Wayne | 4346 | 26653 | 160 | 19038 |
| 10/1/2018 | Wayne | 5738 | 22819 | 137 | 16299 |
| 11/1/2018 | Wayne | 4806 | 15197 | 91 | 10855 |
| 12/1/2018 | Wayne | 3806 | 9679 | 58 | 6914 |
| 1/1/2018 | Grand Rapids | 15711 | 24549 | 147 | 20040 |
| 2/1/2018 | Grand Rapids | 20800 | 37805 | 226 | 30862 |
| 3/1/2018 | Grand Rapids | 23515 | 51717 | 310 | 42218 |
| 4/1/2018 | Grand Rapids | 34522 | 66282 | 397 | 54108 |
| 5/1/2018 | Grand Rapids | 44888 | 81503 | 488 | 66533 |
| 6/1/2018 | Grand Rapids | 50294 | 83467 | 500 | 68136 |
| 7/1/2018 | Grand Rapids | 43471 | 71192 | 426 | 58116 |
| 8/1/2018 | Grand Rapids | 57824 | 87394 | 523 | 71342 |
| 9/1/2018 | Grand Rapids | 44602 | 74465 | 446 | 60788 |
| 10/1/2018 | Grand Rapids | 50843 | 60882 | 365 | 49699 |
| 11/1/2018 | Grand Rapids | 30890 | 38869 | 233 | 31730 |
| 12/1/2018 | Grand Rapids | 14620 | 23813 | 143 | 19439 |
3 Replies
Can you create another table for only for "Locations" and create relationships off the new table? Then use the slicer from the new table.
v-xjiin-msftSolution Sage
Hi rbfyfe,
What's Sales Data and what's Goal Data? What's your logic to compare the two values and what's your expected result?
Thanks,
Xi Jin.Sales Data:
Each row represents an order.
Each Order has a "location" a "sales date", and a "Dollar amount". The labels above are somewhat misleading.
Goal Data:
Each Row represents multiple things -
"A Month", "A location"
Along with 3 types of data
"2017 Historical"
"2018 Plan"
"Model Revenue"
"Shoots Plan" (this data point is NOT necessary or included in the above graph).
The ultimate goal is to create a bar graph, that shows
2018 Plan, Model Revenue, Actual Revenue BY Month based on Location.
*Key Point*
I am managing user roles to only allow specific email addresses see specific locations.
Case examples being, For the month of March, I see that the "Red Bar's" (or Actual Revenue) height is below the "Green Bar's" (2018 Plan) and below "Blue Bar's" (Model Revenue), meaning I missed my goal for the month of March. (They are not wondering how to flip between locations)
