Forum Replies Created
-
Posts
-
Hello Warren,
From what I can see, you are taking all the right learning steps.
In Rob’s 22 hour course, there is a lesson near the end of the “warm-up” section entitled, “Thinking Like the Power Pivot Formula Engine” and an accompanying handout, the “Reference Card”. This became the keys for me that opened the door to successfully thinking through and writing DAX formulas (even towards the end of the course when Rob gives lessons on many-to-many relationships).
Regarding the start date, you could add that to your table for hardware, but if you do not have a record of cost for every month, how could Power Pivot give you costs spread out over year(s), quarters and months? What a start date would give you though is the ability to add future or planned systems into you costs table (ooh – “what if” scenarios could be good to offer…)
Besides, if Power Pivot could figure out all of the costs for a system from a single record, wouldn’t we have to find other (less interesting) jobs? 🙂
Hello Warren,
Regarding how I created the table, I created the new table from two tables that already had I had already imported into the data model (“Hardware” and “Months”) using a method described when you follow this link:
http://www.sqlbi.com/articles/linkback-tables-in-powerpivot-for-excel-2013/
The blog that you will read when following the link above describes how to build a linkback table, a feature available in Excel 2013, but not 2010.
One of the cool things about Power Pivot is that, given time, there always seems to be a better way to do something. For example, I woke up this morning and thought: I didn’t need the Months table, I could have created my new table by combining the Hardware table and the Calendar table…
Also, I created the data model in this way after learning how the formula engine works (there are about 6 steps that the formula engine follows over-and-over-again to get results) and how the formula engine uses relationships to lookup and filter data.
I recommend Rob Collie’s book, “DAX Formulas for PowerPivot”. It was a game changer for me because it gave me technical skills to produce while giving me a handle (attitude) regarding the learning curve before me (see Chapter 3, “Learning Power Pivot ‘The Excel Way'”.
Another comment: This example has some advanced components, but they are mostly in the preparation of data. If your employer would approve a few rows of sample hardware/system data with and without cancellation dates (you could change the serial number, vendor ID, etc.), I could make the example more specific to your needs (which, in turn, would prompt more questions). The most important part would be the structure (column names and data types) of the data, not the content.
Also, if you decide to pursue Power Pivot, there is an Excel add-in (free) that you should download from Microsoft: Power Query. Useful when you need to (= often) clean-up and transform data before importing into Power Pivot.
Also, once a month or so, powerpivotpro.com (the host of this forum) offers a free webinar for help those new to Power Pivot and DAX to get started. I recommend this webinar also.
Hello Warren,
Attached is a workbook with some sample data and simple pivots.
A little background before you open the workbook:
Since your tracking table only has one row per system, that will make it a dimension table.
You also need a data (fact) table to work with the calendar data. The data table will have one row for each serial number and for each month that a maintenance agreement is in place. It does not matter how it is made, that can be a complexity that you leave up to your IT people, but I created such a data table with a single Power Pivot formula (using a table that represents hardware information and a list of dates that represent the months covered in the report). In Power Pivot, I named this table “Costs” and the column format is:
SerialNo, Monthly Maintenance Cost, Month.
In order for Month column in the hardware table to relate to the calendar table, it must be a date, so I choose the first day of each month to represent a month.
The calendar table also was marked as the date table inside of Power Pivot (Power Pivot window => Design tab => Mark as Date button).
I added my own calendar table.
The Power Pivot model is ready to go to work, but it only has fictitious data.
At this point, it would be easy to create monthly, quarterly and annual expressions using Power Pivot’s formula language (DAX).
DAX formulas currently in the model include:
Volume:=DISTINCTCOUNT ( Costs[SerialNo] )
Sum of Cost:=SUM ( Costs[MonthlyMaint] )
All Cost:=CALCULATE ( [Sum of Cost], ALL ( Costs ) )
% of Cost:=DIVIDE ( [Sum of Cost], [All Cost] )
Feel free to ask any questions.
Tom
In case you are wondering what the single formula looks like that created the Costs table:
EVALUATE
SUMMARIZE (
FILTER (
CALCULATETABLE ( CROSSJOIN ( Hardware, Months ) ),
ISBLANK ( [CancelDate] )
|| [MonthStart] < [CancelDate]
),
[SerialNo],
[MonthlyMaint],
[MonthStart]
)
ORDER BY [SerialNo], [MonthStart]Warren,
I believe all of your metrics will be easy to resolve once your tracking table is in a format that will have a working relationship with the calendar table.
If I understand your data right, your tracking table contains one record for each piece of hardware, and the monthly amount is for “any” month (other columns do exist, but this would be a meaningful place to start). If so, we may need to reshape it a little to work with the calendar table.
My objective is to have a table that will have a working relationship with the calendar table so Power Pivot can do all of the data crunching for you. I think we are close to a solution.
I will be in a meeting for the next 2 hours, but depending on your answer to the second paragraph I can put together a couple of ideas for a solution in an Excel 2010 or 2013 workbook. So I should probably ask if you have Excel 2010 or Excel 2013? (Either one will be fine, but Excel 2013 offers at least one option that is not in 2010).
Also, is your overall reporting period of analysis (at least to start) something like the last 12 months or current calendar year?
Tom
Warren,
Thanks for the feedback, it helps.
A couple of clarification points for me:
As I picture your tracking table, you have columns for
Serial Number
Monthly Cost
Year (of Cost)
Month (of Cost)
Cancellation Date (date system was decommissioned)You may or may not have other columns, which would could be of interest later on (such a vendor ID), but for now, the columns listed above for your tracking table are important. If monthly costs are not recorded or projected after a cancellation date, Cancellation Date may not be important.
The values you are interested in are listed as:
Volume/count, percent of cost, etc.
Regarding “percent of cost” are you referring to individual measurements for % of monthly, quarterly and annual costs?
Does “count” refer to the number of systems currently under maintenance?
I am not sure what is meant by “Volume” (or maybe volume divided by count is a single metric), if you could help me understand this term better, that would be great.
Looking forward to your response,
Tom
Hello dspblues,
I would like to know a little more about the point-of-view on the maintenance: is your company an entity that provides maintenance to other companies/households or are you a business that has maintenance agreements on equipment that you own?
The answer to the above question is key to working toward the right answer(s) for you.
Your records in the tracking table should also have a date when maintenance was performed (does your table also contain “scheduled” (future) maintenance dates?).
Your calendar table should have one-and-only-one date record for each calendar day for the period of analysis–no gaps or duplicates.
Ideally your calendar table would already have separate columns indicating year, quarter and month for each day.
Hello Gaetan,
You are welcome!
Your success has become my success, too.
Try something like:
Average with Criteria :=
CALCULATE (
AVERAGE ( JobCodeApplicants[Applicants] ),
JobCodeApplicants[Applicants] <> 0
)JobCodeApplicants is the table name and Applicants the column that contains the quantity. If you put the the Job Codes on rows (and the measure above in values), Power Pivot will figure out the rest
Hello Rhino,
It would make more sense to me that you use SQL before importing into Power Pivot, as you mentioned a couple of posts ago. But I think I understand what is going on.
Take a look at the attached spreadsheet. Go to Power Pivot window and check out the results.
Your “date” column is probably imported as text. For the formatted columns, check-out that the ones that convert correctly were formatted using a date-type column, but then look at the Text_Conversion column that is based on a column that in all respects looks like a date, but is actually text.
To demonstrate, select the Login_Date_As_Text column within the Power Pivot window. Then, on the Home tab, in the Formatting options group, change the data type to date. After the data type changes, you will see an interesting change in the Text Conversion column.
Larry,
I have used linkback when the original data was brought directly into PowerPivot via Power Query.
Earlier experience, similar to yours, was more along the lines of I imported (saved and loaded) into an Excel worksheet and also into Power Pivot.
If you have some sample data, it does not have to be (and should not be in this case), real data. But the structure of the model and how you are importing is important. It probably would only need to be a few rows in each table.
Would like to give it a try.
Hello Rhino,
When you imported the data into power pivot, what did you name the table for this structure:
UserName | Department | Login_Date
============================A | HR | 2015-03-25
B | Research| 2015-04-12
C|Finance| 2015-04-25Your formula should follow this pattern:
=FORMAT(‘YourPowerPivotTableName'[Login_Date],”MMM”)
When I use the formula:
= FORMAT( Month[Login_Date], “MMM” ) or = FORMAT( Month[Login_Date], “MMMM” ) I get the expected month
Your formula
Format((Month[Login_Date]),”MMM”)
appears to have some extra parentheses — before Month and after ] — . Maybe since there is a DAX function MONTH, the extra parentheses are sending the wrong message. Also make sure your formula begins with an equals sign.
Hello Rhino,
Let me evaluate your sample and will get back to you shortly.
Hello Larry,
There may be a way of achieving your goal using linkback table(s), a feature that Power Pivot for Excel 2013 has (but Excel 2010 does not). I first considered using a linkback table, but did not know then whether you had version 2010 or 2013. Do you have some sample data that I could use to evaluate this option. The data can be fictitious (names and quantities replaced), but the structure of the model should be consistent with the “real” model.
If you would like to read-up on linkback tables, the following link is a great place to start:
http://www.sqlbi.com/articles/linkback-tables-in-powerpivot-for-excel-2013/
Let me know where you want to go from here.
Hello Gaetan,
Thank you for the kind words; I am pleased to hear about your success with the dashboard design.
