Excel PowerPivot DAX Calculated Field -

i think i've got relatively easy problem here on hands, having trouble getting work. let me preface saying i'm new dax.

consider following powerpivot data model :

it consists of sales records table, joined date lookup table, "daily expenses" table. "daily expenses" table stores single value represents averaged cost of running specific trading location per day.

it's extremely easy produce pivottable provides me total sales amount per store per [insert date period]. i'm after dax formulation calculate profit per store per day - ie. total sales minus dailyexpenses (operating cost):

in theory, should pretty easy, i'm confused how 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 appreciated.

zam, unfortunately failed attempts not close :-)

the answer, , best practice, use 4th table called 'stores' contains unique record per store - not useful bringing data 2 fact tables can contain additional info stores can use alternative aggregations e.g. format, location etc.

you should create relationship between each of sales , expenses tables , store table , use measures like:

[sales] = sum(salesinformation[saleamount]) [expenses] = sum(dailystoreexpenses[dailyexpense]) [profit] = [sales] - [expenses] 

provided have date , store tables correctly linked 2 'fact' tables (ie sales , expenses) whole thing should line nicely.

edit:

if want roll weeks, years etc. , have no kind of relationship between expenses , calendar you'll need adjust expenses measure accordingly:

[expenses] = sum(dailystoreexpenses[dailyexpense]) * countrows(datetable) 

this take number of days in particular filter context , multiply expenses it.