Excel PowerPivot DAX Calculated Field

Question

I think I've got a relatively easy problem here on my hands, just having trouble getting it to work. Let me preface this by saying I'm new to DAX.

Consider the following PowerPivot Data Model :

It consists of a Sales Records table, which is joined to a Date lookup table, as well as a "Daily Expenses" table. The "Daily Expenses" table stores a single value which represents the averaged cost of running a specific trading location per day.

It's extremely easy to produce a PivotTable that provides me with the total Sales Amount per store per [insert date period]. What I'm after is a DAX formulation that will calculate the profit per store per day - ie. Total Sales minus DailyExpenses (operating cost):

In theory, this should be pretty easy, but I'm confused about how to utilise DAX row context.

Failed attempts:

Profit:=CALCULATE(SUM(SalesInformation[SaleAmount] - DailyStoreExpenses[DailyExpense]))

Profit:=CALCULATE(SUM(SalesInformation[SaleAmount] - DailyStoreExpenses[DailyExpense]), DailyStoreExpenses[StoreLocation])

Profit:=SUM(SalesInformation[SaleAmount] - RELATED(DailyStoreExpenses[DailyExpense])

etc

Any help appreciated.

Answer 1

Zam, unfortunately your failed attempts are not close :-)

The answer, and best practice, is use a 4th table called 'Stores' which contains a unique record per store - not only is this useful for bringing together data from your two fact tables but it can contain additional info about the stores which you can use for alternative aggregations e.g. Format, Location etc.

You should create a relationship between each of the Sales and Expenses tables and the Store table and then use measures like:

[Sales] = SUM(SalesInformation[SaleAmount])
[Expenses] = SUM(DailyStoreExpenses[DailyExpense])
[Profit] = [Sales] - [Expenses]

Provided you have the Date and Store tables correctly linked to the two 'Fact' tables (ie Sales and Expenses) then the whole thing should line up nicely.

Edit:

If you want to roll this up into weeks, years etc. and you have no kind of relationship between expenses and the calendar then you'll need to adjust your expenses measure accordingly:

[Expenses] = SUM(DailyStoreExpenses[DailyExpense]) * COUNTROWS(DateTable)

This will basically take the number of days in that particular filter context and multiply the expenses by it.