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.