On Friday I blogged about how to create a composite key to establish a relationship between two tables imported into a Tabular Model from different data sources. My example used a report extract table containing sales data in a relational database and an Excel spreadsheet with sales projections. After making the relationship, the first thing you’ll want to do with it is to compare the actual sales data with the sales projections to see if you are meeting sales targets. Since projections were made by store and year, you’re going to want to compare them to the total sales by store and year.
Sales Difference Calculation
To make the comparison you have to write DAX code that sums the actual sales data and then subtracts the projection to determine by how much the actuals exceeded or under delivered against the projection. To do this we’ll create a new column in the SalesProjections table and call it SalesProjDiff. The column is defined using this DAX code:
=SUMX(FILTER(ReportExtract, ReportExtract[YearStoreType] = SalesProjections[YearStoreType]), ReportExtract[SalesAmount]) – SalesProjections[ProjectedSales]
This new column uses the SUMX function to sum sales by year and store type and then subtracts the sales projection for the same year and store. To ensure that the correct sum is compared with the right projection value, make sure the filter uses the composite key you created that defined the relationship between the two tables that we called YearStoreType in each of the tables. When this is done you now have a column called SalesProjDiff that users can access in client visualization and reporting tools. This allows them to see how sales have done versus projections in the past, and also to measure how current sales are tracking against future projections.
I’ll cover more data modeling scenarios like this one using Tabular Models in my session at the PASS Summit titled Data Modeling Best Practices for Enterprise Tabular Models at 10:15 AM on Thursday, November 8.
Even though it’s been more than a month, there’s never a bad time to talk about some things and the PASS Summit 2011 is one of those. Like a lot of people I returned from the Summit and was instantly slammed with work and other personal things I had neglected for a week. Now that the madness has subsided I feel the need to write about this year’s Summit and to try to turn others on to it if they missed it.
The myth: On the surface you might think the Summit is a boring trip back to your school days, when you had to sit and listen for days on end to that economics professor talking about macroeconomic theory. Could there be anything drier than meeting with a bunch of DBAs and attending sessions on clustering, indexing and reporting?
The truth: The Summit is a vibrant event full of positive atmosphere and lively personal interaction. There are people all over who want to help you, meet you, and talk to you. Learning, teaching and discussing with others, that’s what the Summit is all about.
Outside of the sessions I attended, I spent 90% of my time at the Summit meeting people, shaking hands, introducing myself, hearing about their backgrounds, handing out my contact info, and reconnecting with SQL friends. Even with all that networking, I still didn’t get to meet everyone I would have liked to meet. I attend a conference like this with several goals in mind and one of those is to introduce myself to people who have sparked my interest because of the work they do or something they’ve published.
There’s so much going on that I couldn’t fit everything in that I wanted to do. I just waived hello in passing to several familiar people but didn’t get a chance to talk to them. I completely missed a few people that I really wanted to meet. One was Mark Ginnebaugh (B|T), who seems to run everything in the Bay Area. I wanted to get together with Mark because I have a reason to be in SF at times and he seems like the guy to know. I also wanted to talk to Audrey Hammonds (B|T), who had written a blog post that stuck with me urging women to speak at technical conferences.
That’s why I’m going back next year, to talk to the people I missed, and to meet the people I didn’t.
Today is the day I’m presenting at the PASS Summit. My session is at 3pm in room 609-610 and is titled See the Future with Visual Predictive Analytics. It shows you how to do some cool things with Reporting Services and Data Mining in Analysis Services by creating predictive models that drive the reports. However, it’s more of an idea session that I hope will put a spark in people when they go back to work next week. They’ll be able to take what they see in the session back to their companies, apply it to their own projects, and do some things that will provide them with a big bang for the buck and a great ROI, which we all know management loves.
I hope to see you there and please say hello! I welcome any questions and conversation you might have.