• Excel - PivotTables

    Using HASONEVALUE in a DAX IF statement 

    by
    Ken Puls
    Published on 2012-12-17 03:50 AM     Number of Views: 1722 
    Article Preview

    As an accountant, I build financial reports, and one of the issues that we have to deal with is getting the numbers to display in a friendly format. Because of the way that debits and credits are stored in databases though, this can be a little challenging.

    In this article, I’m going to walk through the process of building a simple profit and loss statement with PowerPivot, showing how to make all values show correctly. There are some certain key issues that we’ve got to work through though, and we’ll do that using a conditional DAX measure.
    ...
    Read More Read More 1 Comment

    Hide Calculated Items With Zero Totals In PowerPivot PivotTables 

    by
    Ken Puls
    Published on 2012-12-13 05:02 AM     Number of Views: 2138 
    Article Preview

    The method for hiding items with zero totals in a PivotTable is different if you're working with a regular PivotTable or a PowerPivot PivotTable. This article focusses on how to accomplish this goal in the PowerPivot version. (If you're working with a regular and you want to hide calculated items that have zero balances, you'll want to check out Debra Dalgleish's blog post on the subject.)

    To start, assume that we’ve got a fairly simple PowerPivot pivot table that looks like this: ...
    Read More Read More

    Creating a Spacer Column in a PowerPivot PivotTable 

    by
    Ken Puls
    Published on 2012-12-13 04:50 AM     Number of Views: 768 
    Article Preview

    Mike Alexander has a great blog post on how to Add Column Spacing In A PivotTable. We can also accomplis the same thing through a PowerPivot solution, but using DAX. And in this case, DAX is even easier to use that the old method, taking one less step!
    ...
    Read More Read More

    Retrieving Selections From A PivotTable Slicer 

    by
    Ken Puls
    Published on 2012-05-02 07:45 AM     Number of Views: 7353 
    Article Preview

    So you've built a really cool PivotTable, and you hooked up a slicer to allow exploration of the data. And now you want to do something really cool, but you need to make your formula react to the slicer value. Can you do it? Of course you can, but how?

    This article will focus on the technique to do exactly that: return the value of a slicer to a formula. Note that, in order to follow along you will need Excel 2010 or higher, as Slicers didn't exist prior to this version.
    ...
    Read More Read More

    The Magic Of PivotTables (2010) - Video Course 

    by
    Ken Puls
    Published on 2012-04-24 08:10 AM     Number of Views: 7375 
    Article Preview

    Excel PivotTables: It’s a polarizing term. People who use PivotTables absolutely love them. For those who don’t, the term is mysterious and encourages the fear of powerful features that are the domain of geeks and Excel junkies, and out of reach to the common man. But nothing could be further from the truth.

    Why You Should Take This Course:

    If you’ve never created, or don’t regularly use PivotTables in your work, let me show you that you are missing out on one of the most useful, impressive and easy-to-use tools in Microsoft Excel.

    Excel PivotTables are an amazingly powerful feature that can be used to very quickly summarize and slice and dice data with ease. And contrary to many users’ fears, they are actually VERY easy to use once you’ve been shown how.

    So why don’t you let me do just that? In this one hour video training course, I will teach you how to build your first PivotTables. You’ll see first hand just how easy they are to create, how fast they work, and how easy it is to change them to display your data the way you want to see it.
    ...
    Read More Read More 2 Comments

    The Magic Of PivotTables (2007) - Video Course 

    by
    Ken Puls
    Published on 2012-04-24 08:07 AM     Number of Views: 2512 
    Article Preview

    Excel PivotTables: It’s a polarizing term. People who use PivotTables absolutely love them. For those who don’t, the term is mysterious and encourages the fear of powerful features that are the domain of geeks and Excel junkies, and out of reach to the common man. But nothing could be further from the truth.

    Why You Should Take This Course:

    If you’ve never created, or don’t regularly use PivotTables in your work, let me show you that you are missing out on one of the most useful, impressive and easy-to-use tools in Microsoft Excel.

    Excel PivotTables are an amazingly powerful feature that can be used to very quickly summarize and slice and dice data with ease. And contrary to many users’ fears, they are actually VERY easy to use once you’ve been shown how.

    So why don’t you let me do just that? In this one hour video training course, I will teach you how to build your first PivotTables. You’ll see first hand just how easy they are to create, how fast they work, and how easy it is to change them to display your data the way you want to see it.
    ...
    Read More Read More

    The Magic Of PivotTables (2003) - Video Course 

    by
    Ken Puls
    Published on 2012-04-24 08:03 AM     Number of Views: 2219 
    Article Preview

    Excel PivotTables: It’s a polarizing term. People who use PivotTables absolutely love them. For those who don’t, the term is mysterious and encourages the fear of powerful features that are the domain of geeks and Excel junkies, and out of reach to the common man. But nothing could be further from the truth.

    Why You Should Take This Course:

    If you’ve never created, or don’t regularly use PivotTables in your work, let me show you that you are missing out on one of the most useful, impressive and easy-to-use tools in Microsoft Excel.

    Excel PivotTables are an amazingly powerful feature that can be used to very quickly summarize and slice and dice data with ease. And contrary to many users’ fears, they are actually VERY easy to use once you’ve been shown how.

    So why don’t you let me do just that? In this one hour video training course, I will teach you how to build your first PivotTables. You’ll see first hand just how easy they are to create, how fast they work, and how easy it is to change them to display your data the way you want to see it.
    ...
    Read More Read More

    Sorting A Column Of PowerPivot Data By Another Column 

    by
    Ken Puls
    Published on 2012-03-28 06:16 AM     Number of Views: 4072 
    Article Preview

    One of the things that used to drive me crazy about working with PivotTables in PowerPivot’s initial (2008) release was summarizing dates by month. With a standard PivotTable, we can use the built in Group functionality to group dates by Years, Quarters and Months. But in PowerPivot, that functionality wasn't implemented. To deal with this, we have to provide our own date table, but the months never really sorted well, and we had to resort to tricks to coerce them into the right order.
    ...
    Read More Read More

    An Interesting Use For Slicers 

    by
    Ken Puls
    Published on 2011-04-06 07:11 PM     Number of Views: 243 
    Article Preview

    Over the past while we’ve been building a Dashboard report for our golf course. It’s got some historical information in it, but we’ve also pulled in things like weather forecasts. The intention is that our managers will be able to see where we’ve been over the past week, as well as look at the key measures that will allow us to staff appropriately for the next week.

    We put out a prototype of the Dashboard report, and our Director of Golf said “This is cool. What’s the chance we could also have the events coming up over the next week listed?”
    ...
    Read More Read More

    Write-back Using PowerPivot 

    by
    Ken Puls
    Published on 2011-03-10 07:43 PM     Number of Views: 325 
    Article Preview

    In a discussion about PowerPivot yesterday, one of my friends stated that it wasn’t really useful since you couldn’t perform writeback using PowerPivot. To him this is a very important piece in the Excel budgeting process. Now, I agree that PowerPivot doesn’t give you write-back to a database, but this got me thinking; we have linked tables, so why couldn’t we create a write-back loop for a model that was built entirely in Excel? Well, we can!
    ...
    Read More Read More

    Data Validation in Excel Services 

    by
    Ken Puls
    Published on 2011-03-08 08:09 PM     Number of Views: 562 

    I’m a huge consumer of Data Validation in Excel. At this point, however, Excel services (the Excel webApp) is still so new that there are very few of the techniques that we normally use in Excel which are web compliant. This article looks at ways that we can implement data validation into an Excel services solution.
    ...
    Read More Read More
Gravatar by 1e2.it