This topic contains 74 replies, has 2 voices, and was last updated by 
-
Posts
-
Warren,
A calculated column sounds great.
Keep me up-to-date; looking forward to working with you.
Will do.
Another question on approach. So, we are on a mission to decommission these systems by 30% a year. That’s the goal.I’ve got a decent handle on the “actual” costs, I think.
For the goal costs & volume, I created a small table that has the months from 1/2015 – 12/2018. 30% reduction at current volumes, not too complicated.
Now.. my issue is I have lots of great info in the tracking sheet. Line of business, vendor, tier of system, etc.. all by serial #. I’d like to build a relationship between that data and these goals, but we honestly have no idea what will be decommissioned.
So, I’m thinking I need a table with goals by platform, line of business, etc.  Can you think of an easy way to accomplish this? Sounds like another linkback table. 🙂  To clarify, the 30% reduction is all based on 1/2015 volumes. By the end of 2017 I’d be down 90%.
I was thinking a linkback table might be good because when they want to be able to add another way to filter the data I could update the DAX expression and update the table to include it.
Warren,
Some good on thoughts on use of linkback table, but maybe Power Query could also find a fit here.
A question: Is it your company’s decision which systems to commission or is there an outside entity that is making the decisions?
I have up-to-now assumed it was your company’s decision, but maybe I am wrong.
Continuing my assumption, with pivots and slicers you could play with ideas like, “is it best to think of this project as decommissioning individual systems or could the project approach be better thought of as decommissioning vendor systems (removing all systems by a vendor as a group could also save bookkeeping and check writing expenses) or decommissioning systems by line of business, decommissioning systems by location, etc.” If you haven’t already done this, there could be some lively conversations that follow.
Lively conversations can be a good thing.
Hi Tom,
It’s my company who decides what goes on/off maintenance. We’ve also seen situations where systems that were’t on maintenance need to be added back.
The plot thickens!
Makes this more complicated.
You are very right. There are a lot of “what if” scenarios that play out here. By server function, internal customer, etc. So having all of this linked together would help. I’m not familiar with Power Query, but I’ll have to take a look.
Question. So… I’d like for all my stats to reflect my “goal” of 30% a year, based on Jan 2015 volumes & cost.
I have this working, kinda. I created a simple table that shows that monthly reduction in volume and cost, but since there’s no relationship between that and my other SN data, it’s limited in use.
Is there a better way to all of this with measures?
Hello Warren,
Let’s think this one through.
You have a table for your goals the shows monthly reduction in volume and cost, that I think has columns like: Year-Month, Volume Reduction Goal (units), Cost Reduction Goal (currency).
You have a tracking table that has a cancellation date that can be represented also as a year-month.
So if you created a relationship between those two tables on year-month columns, you would have serial numbers related to your goals.
There’s more to tell on how this can be worked out, but I would like you would to consider this idea of reaching serial numbers through a table relationship based on year-month because it can bring us to the point of creating measures and pivots.
You are correct. That’s exactly what I have. every month is a row, and a column for volume goal and cost goal. Â It has no understanding of the goals for LOB or vendor (they are the same, 30% yoy).
The tracking sheet does have a cancellation date, and has all that wonderful data for each SN.
I guess since my goal table doesn’t understand SNs, how will a date relationship help between these 2 tables? I was actually starting to wonder if I even need the goal table, if that goal could be in the form of a measure … or I dare say a selectable slicer! :-O
Warren,
Appreciate your feedback; there is much to consider there.
A few thoughts.
It never hurts to have your goals written out and “translated” into month to month requirements for success. Sadly, most people in the work-a-day world just go though motions because they never put things together, like “I want to be at x in three years, this is what I need to do month by month to get there.” Having your goals written down, together with a plan, puts you in the top 3% of workers.
Because you have measures and slicers you want to implement, I say, go for it!
I ask though that you do not delete your goal table, just set it aside and consider it may fit into your plans down the road.
That you observe that the goal table does not know anything about serial numbers — on its own or by itself — is a good observation, but does not mean that there cannot be any power or consequence to the relationship with the tracking table (powerful relational databases are built on this principle).
So let me know if (yet or when) I can be a resource in the Power Pivot/Power Query side of things.
It sounds like you have a good handle on things (I think what is needed is the powers-that-be at your company need to make some decisions about when the systems will be decommissioned; sounds like they know the “why” and the “what” and the “where”).
I’m not sure how to write these measures yet. No idea. 🙂 I’ll keep thinking about it.
I think another goal table, created as a linkback table could be created. I think that would be a way to create a goal table with all the other information. But maybe it’s not needed. Hmm.
Warren,
I have an idea I need to work out; will get back to you tomorrow.
Tom,
Yay! I was able to figure it out. A big accomplishment for me since I started on PP a little over a week ago.
So… I started by calculating my volume baseline measure:
Vol Baseline:=CALCULATE([Actual Volume], all(‘Calendar’), Costs[Cost Month]=1, Costs[Cost Year]=2015)
This give me the start volume and I’m able to use the tracking table against it. So, I can easily filter and see those counts for product, line of business, whatever.
I added a new column to my goal table. 2.5 % reduction every month.
I then created this measure:
Sum Reduction Goal:=Sum([Goal Pct Reduction])
Since this goal aggregates as the months go on, my next measure does just that:
Aggregated Reduction Pct Goal:=CALCULATE([Sum Reduction Goal], Â FILTER(ALLSELECTED(‘Calendar’), Calendar[FullDate]<=MAX(Calendar[FullDate])))
Jan/2015= 2.5%, Feb = 5%,….. 12/2017 = 90%.
(I found this online) 🙂  I saw Rob do something like this in the videos, but I couldn’t find it.
Finally, my aggregated vol goal:
Aggregated Vol Monthly Goal:=[Vol Baseline]*(1-[Aggregated Reduction Pct Goal])
My only issue now, I think, is that when I filter on the year slicer, I lose my correct % total. I’ll fix that tomorrow, I hope. Â I can do all of this again for the financial goals.
Does this look like I’m on the right path?
Warren,
Progress is good!
Congratulations on your find and implementation!
Tom,
Can you explain how your linkback table DAX formula works? I’m thinking I may want to add maintenance start, forecasted cancellation date to the tracking list and have that flow into the costs table.
Warren,
Let me give the conclusion first:
Any other column from the hardware/tracking table you want to add to the cost linkback-table should be fine (just make sure that you at least keep the columns for serial number, monthly maintenance amount and the month-start date).
The explanation is long-winded, but I think I have covered the key points. First, there is the formula, followed by explanation of the clauses.
EVALUATE
SUMMARIZE (
FILTER (
CALCULATETABLE (
CROSSJOIN (
Hardware,
SUMMARIZE (
FILTER ( CALENDAR, [DayOfMonth] = 1 ),
[YearMonth],
“MonthStart”, MIN ( Calendar[Date] )
)
)
),
ISBLANK ( [CancelDate] )
|| [MonthStart] < [CancelDate]
),
[SerialNo],
[MonthlyMaint],
[MonthStart]
)
ORDER BY [SerialNo], [MonthStart]Step 0: Every linkback table begins with the keyword EVALUATE
Step 1:
The CALCULATETABLE section creates a table based on a CROSSJOIN between the hardware table and the table for months. A cross-join simply pairs each record from one table with all the records from another table.At the end of Step 1, if — to start — you had 100 hardware records and 48 month records, the resulting table would have 4800 rows.
Also at the end of Step 1, the resulting table has all of the columns from the hardware table and the table for months. So if the table for hardware had 25 columns and the table for months had two columns, the resulting table would have 27 columns.
Step 2:
The outer FILTER function only keeps rows in the newly created table when they meet the specification:
ISBLANK ( [CancelDate] ) || [MonthStart] < [CancelDate]
(if a row in the newly created table for a system has no cancel-date or if the month-start for the row precedes the cancel-date, then row stays, otherwise it is discarded)
So if serial number “ABC” has no cancel date, all 48 of its records are left alone. If serial number “DEF” has a cancellation date, records with a month-start prior to the cancellation date are left alone, all other records for serial number “DEF” are deleted.
Step 3:
At the end of Step 2, we have no “extra” rows, but we may not need all 27 columns of the table described in Step 1.
The SUMMARIZE step, as used in the linkback table formula, tells Power Pivot that in the output we only want Serial Number, the Monthly Maintenace amount, and the MonthStart column.
Step 4:
The ORDER BY orders the result set by Serial Number first then MonthStart.
Conclusion: If you wanted to keep more columns from the hardware tracking table, just make sure they are referenced in Step 3.
Tom,
Thank you for the explanation. I’ll be working on this, but it’s brought up a question..
I’m wondering the best way to handle forecasted decommissions. I have a few options.
- I could have a column in my tracking table that lists the forecasted decommission dates.
- I could leave those dates in the cancellation date column, then add a column that lists “actual” or “forecasted”.
- Any others?
Given how the cost table is generated with the linkback table, it would seem like the easiest way to do this is to leave the dates in the same column.
I’m concerned about the long-term implications of this decision. Any thoughts?
The forum ‘Power Pivot’ is closed to new topics and replies.