Mar 2019
1:23pm, 15 Mar 2019
18,153 posts
|
flanker
I'll go for the easy one first - pie chart with total 45 - slice of 40 with key 'Allocated' in a nice pale green, slice of 5 with key 'EXTRA chairs required' in a bright red. With a second chart if you wish showing division between team in the off chance anyone cares or cost is apportioned by chair!
Curly - where is the price history? Without the previous weeks price stored, how are you going to have a clue about the difference? If you had a price history table - either running or just current and previous week - keyed by date or date code then it would be pretty simple. Are you in a position to rethink the Table A design?
|
Mar 2019
1:28pm, 15 Mar 2019
3,863 posts
|
Curly45
Gregp surely you do that by showing each chairs use as more than one person? Time share your chairs by occupancy rate. I. E. Team A has 25 people but only 90% use chairs at any one time, Team B 10 but 75%, Team C 10 but 55%.
Then express percentage occupancy as a likelihood of needing X chairs at any one time = should fit on a pie chart.
Do forget the musical chairs music 😂😎
|
Mar 2019
1:30pm, 15 Mar 2019
3,864 posts
|
Curly45
Price history is in table a.
Every week has enough rows for each product.
I can't change design as I have 17 X 3 relationships set up that power a pivot table on the front end that looks up price via an internal code on all retailers at once... Don't want to mess with this.
|
Mar 2019
1:40pm, 15 Mar 2019
111,274 posts
|
GregP
I've done something in Powerpoint with a piece of pie that is too big for the chart. Flanker's idea's a good one though.
Curly made my head hurt again - second time today :-
|
Mar 2019
1:44pm, 15 Mar 2019
3,865 posts
|
Curly45
I work in an office with 6 chairs and up to 15 people - only rarely is there no where to sit...
|
Mar 2019
2:01pm, 15 Mar 2019
111,275 posts
|
GregP
We're the same - 5 chairs in our office, there's 8 of us. Very rarely a problem. That said I've worked from home three days out of five this week.
|
Mar 2019
11:36pm, 15 Mar 2019
3,369 posts
|
run free
Curly should be done as measures. Break down what you want for each piece: 1. Measure to extract last week's price
2. Measure to extract today's price 3. Measure for the difference of the 2
Don't have time to check yet but for (1) and (2) you'll need to use the CALCULATE function with maybe MAX([Price]) and FILTER using Date table but will need a liitle thought as assume Date Code will need to be converted using WEEKNUM.
Is it possible for a product code to have multiple prices during a week? Am thinking an offer could be on and then removed in the same week.
Gotta go to bed xx
|
Mar 2019
10:34am, 16 Mar 2019
3,866 posts
|
Curly45
Hi Run free thanks for the help. No multiples as price per week is an average already before put into the model (thankfully). Am out for much of today but will go and play with it later.
|
Mar 2019
1:09pm, 16 Mar 2019
3,370 posts
|
run free
Suggest the following
1. PreviousWeek:=CALCULATE(
MAX(TableA[Price]),
FILTER(
'DateTable',DateTable[WEEK NUMBER]=WEEKNUM(TODAY())-1)
)) 2. CurrentWeek:=Will be similar to above except drop the -1 3. Diff:=[CurrentWeek]-[PreviousWeek]
And PivotTable will have a Product column and Diff in Values. Then apply conditional formatting (or use the KPIs feature) for your traffic light system
|
Mar 2019
10:16am, 20 Mar 2019
3,867 posts
|
Curly45
Thank you run free - I have finally got that to work! I do final handover to my client tomorrow so that is very pleasing to be able to hand it over with all of their "nice to have" stuff ticked off.
|