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 :

enter image description here

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):

enter image description here

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.

• Zam: Hi Jacob. Thank you so much for taking the time to respond. Perhaps I didn't explain the question well enough, but the "Store Expense" table is static in that in only contains one value per store. This value is an averaged daily operating cost for the previous financial year, or the notion "we need to make 500ドル per day to cover costs". Any additional help you can provide would be greatly appreciated..
• Jacob: Hey, changed my answer slightly but it basically still stands. Provided you don't have any kind of relationship between Store Expenses and Date tables then it will work.If you really don't want the extra table then you could link 'Sales' directly to expenses and use the store name from 'Expenses' in your pivots and the measures above should work.
• Zam: Hi Jacbob. Thanks again for the reply. I've taken your advice and simply used the SaleLocation name from the Expenses table as the measure for my Pivot and it works as intended. For learning purposes I would be interested in why it doesn't work the other way though (ie. if I used the SaleLocation from the Sales table)
• Jacob: Zam, it doesn't work because the relationships are effectively one way from the 'One Side' to the 'Many Side'.
• Zam: ...one last question, if you're up to it. The solution works at a day-by-day level, but if I add Grand Totals (row) to the table then they calculate the sum of GP for the total week (Sunday-Saturday) and then simply subtract the daily expense. This is of coarse wrong. Any tips to point me in the right direction?