Over on the Forrester Blog, Boris Evelson has written an interesting post about the real cost of creating a single BI report in the enterprise. He estimates a price tag of between $1,840 and $20K per report after one factors in expenditures on software, services, salaries, hardware etc.

He suggests four general approaches to lowering BI costs, and PowerPivot neatly enables at least two of the four strategies.

Is PowerPivot a BI panacea? Nah, of course not. But intelligently leveraged, I believe the tool will drive down costs associated with creating BI reports.

Over the week-end I was doing some analysis on SQL Server disk usage, and wanted to be able to display current disk usage by database. Up to this point, I’d mainly been doing a SUM over  my measures. Well, that would make no sense in this scenario – I’d end up with a meaningless number just like I would if I took a SUM of an Inventory value.

I found myself dealing with a classic semi-additive measure. These are easy to deal with using SQL Server Analysis Services, but a tiny bit trickier in PowerPivot.

I tried various DAX-related ideas, and some actually kind of worked, but Marius Dumitru, a co-worker at Microsoft, suggested the best solution.

I happen to have a date dimension which we could lean on for this challenge – I just needed to find the last “Disk Use” value that had been recorded. Since all of my readings are associated with a date, slam dunk!

Here’s what I did:

Created a calculated measure which SUMs the measure in my table (I named it “Sum Disk Size”):

     = SUM(‘Fact_DatabaseMetrics’[Disk Size])

Created a second calculated measure which filters the first, looking for the value related to the last associated date in my date dimension (via DAX’s LastNonBlank function) . I didn’t know you could filter a measure so easily!

        = ‘Fact_DatabaseMetrics’[Sum Disk Size](LastNonBlank(‘Dim_Date’[DatePK], ‘Fact_Database’[Sum Disk Size])

The second measure is what I added to my PivotTable.