Problem using DIVIDE and TREATAS to calculate two columns from unrelated tables

[Anonymous]

Hi ,

I am trying to perform a simple divide cost per area for two columns from different tables that do not have a physical relationship. Well it can have a replationship but it would be a many to many relation which is not ideal and doesn't solve my problem. The visual is a matrix table in a heirarchy of season, farm,field and product categories. 

I have tried to create a measure using Treatas 

1. NM Actual cost per area = Calculate(

Divide (SUMX(ActivitiesAlt,[Actual Cost]),  SUMX(Activities,Activities[Cumulative Area]),0),

TREATAS(SUMMARIZE(Activities, Activities[Season],Activities[Farm],Activities[Field],Activities[ Category]),

ActivitiesAlt[Season],ActivitiesAlt[Farm],ActivitiesAlt[Field],ActivitiesAlt[ Category]))

or 

2. NM Actual cost per area = Calculate(

SUMX(ActivitiesAlt,[Actual Cost]) / SUmX(Activities,Activities[Cumulative Area]),

TREATAS(SUMMARIZE(Activities, Activities[Season],Activities[Farm],Activities[Field],Activities[ Category]),

ActivitiesAlt[Season],ActivitiesAlt[Farm],ActivitiesAlt[Field],ActivitiesAlt[ Category]))

but it will not work (see attached pics for relationships and sample data)

the answer im looking for is in the column "Actual Cost by Area".Sample DataRelationships

I thought I could modify my measure to this

NM Actual cost per area = Calculate(

SUMX(ActivitiesAlt,

Divide (ActivitiesAlt[Actual Cost],Activities[Cumulative Area],0)),

TREATAS(SUMMARIZE(Activities, Activities[Season],Activities[Farm],Activities[Field],Activities[Primary Category]),

ActivitiesAlt[Season],ActivitiesAlt[Farm],ActivitiesAlt[Field],ActivitiesAlt[Primary Category]))

But divide won't accept the denominator from a different table, as treatas is performed after this step.

Any suggestions on how to go about this?

[Anonymous]

I'd suggest you improve the model first (unless you want to have huge problems later). I can see you're slicing by Season but Season sits in ActivitiesAlt which looks like a fact table. Do you know, by the way, that in fact tables you should only store foreign keys to dimensions and numbers that come from measuring the process you're modeling? Certainly, an attribute like Company Name is not a number...

Best
D.

[Anonymous]

Thanks Darek,

I realize that my model is not well devised ( it is part of a test for future dev requirements) hence you will see overlapping and duplicating columns in each of the tables. In this case ActivitiesAlt is a transpose of the Activities table which we have created into a separate table just for testing how endpoints should appear. In future, the Benchmarking table would be the Fact table. 

I also understand what you mean about SUMX being an iterator and how it doesn't work in this context. 

I will modify my data model and rework the measure. 

[Anonymous]

In Power BI testing is OK as long as your model is CORRECT. If it's not, your testing will go to waste. I do also understanding prototyping but if your model is going to change in the future, then your calculations - very likely - will have to be re-written as well. A piece of good advice is this: First, get your model right. Then create your calculations.

This means you should first spend time trying to figure out what dims and facts you need and how they should be arranged. You do this taking into consideration the calculations you'll have to perform later on.

Creating a messy model where one field can be found in different places will give you a headache and secondly, will lead to messy DAX.

Please, save yourself the trouble, time and effort. It's not needed to get things right.

Best
Darek

[Anonymous]

Of course, it won't. But it's not DIVIDE that is at fault here. It's your understanding of DAX that's at fault.

SUMX is an iterator. You're iterating over ActivitiesAlt and only this table has a row context inside SUMX. You therefore cannot reference a row from a different table as no other table, apart from the iterated one, has a row context in there.

Best
Darek