0

I'm using Power Pivot add-in to create a data warehouse for generate dynamic tables and graphs (strictly data source is Excel), but I have a problem whit a calculate in the relations. My data model is the following:

My Snowflake data warehouse model

So for the fact table "fSales" I need to multiply the dCostDetail[Value] per dWorkCost[Value] to generate the fSales[Expenses] amount.

I tried to use the formula but I get an error: related but it don't allow to nested between the relations, e.g. fSales[Expenses] = related(dCostDetail[Value])*related(dWorkCost[Value])

Also I tried to use the next formula: 

fSales[Expenses] = related(dWorkCost[Value]) * Calculate(Calculate(Calculate(Value(dCostDetail[Value]), Userelationship(fSales[IdProduct],dProduct[Sku]),Userelationships(dProduct[IdCateg],dCategory[IdCategory]), Userelationships(dCategory[IdCategory],dCostDetail[IdCateg]))))

And I need this "type" of normalized model to have the details when I analyze the information, e.g. filter, but if you know another way to generate the calculation it would be ok.

depperm
  • 10,606
  • 4
  • 43
  • 67
user3579840
  • 1
  • 2

1 Answers1

0

RELATED doesn't work in measures, because it evaluates on a record-by-record level. So you're on the right track, but what you need to do is create a column in Powerpivot in the fSales table called "Cost Detail" or whatever, and use a RELATED formula there to pull in that value from the CostDetail table. Create another column and do the same thing to pull in the dWorkCost value into the fSales table.

Then you can do a measure for the expenses like this:

Expenses:=SUM([whatever you called CostDetailColumn])*SUM([whatever you called WorkCostColumn])

You should be able to drop that measure into a pivot and it should do what you're looking for.

Jeremy Firth
  • 41
  • 4