I just “met” Kasper a few weeks ago via Twitter, and quickly found out he’s a basketball nut – he plays the game a lot, and watches NBA broadcasts at some very strange hours :)
I challenge you to watch this video, listen to his voice, and imagine him as anything other than a seven-foot-tall shot-blocking monster in the lane. Yeah, he claims he is six-foot-three and a perimeter shooter. Sure he is. Self-report is a notoriously unreliable survey technique Kasper, so I’ll just stick to extrapolating wildly from small amounts of unrelated data, OK? :)
Jokes aside, it’s a great video. He shows off some things that I have not, such as pulling data into PowerPivot from an SSAS cube, and using the Remove Duplicates feature in Excel to clean one of his tables. Bravo!
It’s true! honestly :-)Thanks for the link.
Kasper’s video is very well done (Rob, can you handle the competition?) I created a similar model, but used dimensions from the data warehouse tables. I prefer this approach to avoid manual updates in the future, but if you don’t have direct access to the data warehouse tables, Kasper provides a useful alternative.
The PivotChart is pretty meaningless though, because the months are sorted incorrectly.
yes and that is a very big problem in PowerPivot imho, i want to sort on an alternative row (like in SSAS). I have posted this question before (can’t find the link) and this option will probably NOT be in the RTM.
Colin, it is called “co-opetition.” :)
There are a few solutions to the problem of sorting, although none are perfect:
1) Manually drag the members in the order you want. This can be quite tedious if you have a lot of distinct members. Also, if you remove the field from the PivotTable and then subsequently add it, you will have to start over.
2) Use an Excel Custom List. The custom list can be applied to sorts and fills. Excel is equipped with custom lists for days of the week and months of the year. You can use a month custom list in cases where you have a common key field in the tables(which allows you to use month instead of month-year in the PivotTable).
3) Create a new custom list. In the current scenario, we need month-year for table relationships. I created a list from January 2001 to December 2020 (by then we should have a better solution :))
As with (1), you lose the custom sort order under the condition mentioned (and perhaps under other conditions that I haven’t yet investigated). However, it takes less time to reapply the custom sort than it is to drag members manually.
How you apply a custom list for sorting in the PivotTable is *not* obvious. The process is as follows:
1) Select More Sort Options from the field’s sort and filter drop-down menu(CalendarMonth in this case).
2) In the Sort dialog box, click the More Sort Options button.
3) In the More Sort Options dialog box, clear the AutoSort check box and select the custom list from the First Key Sort Order drop-down list. Click OK to return to the Sort dialog box.
4) In the Sort dialog box, choose the Ascending option and select the appropriate field (e.g. CalendarMonth) from the drop-down list. Click OK. Whew!
Ok thanks Colin, only 3 is viable in my option, most data i have (the IRL data) is in Dutch and is not recognized by the default custom lists. I’ll try it soon.