SUMX and RELATED Not Working

[SHS]

Hello, 

I'm having trouble getting RELATED to work in my SUMX Function, though it worked fine Friday and nothing has been changed in the datamodel. 

I have calculated expected turnover by asset based on Qty and Price usign SUMX, including a currency converter variable. After, I wanted to calculate allocation of the expected turnover to HQ, in which I again used a SUMX function taking the expected turnover and multiply by the HQ Discount %, located in another dataset (related to the turnover data by a one-to-many relationship). 

However, this does not work at all. As an example, I have an expected turnover on asset A of appx. 91.000 EUR. With above method, I get an allocation to HQ Discount of appx 5.2 mEUR. 

What am I doing wrong here? I have gone through the files and ensured the data format is correct, I have tested the relationships, and nothing seems to work. As mentioned, it worked fine last week and since I'm used fixed files for testing, it can not be due to updates in data.

[amitchandak]

@SHS , seem like relate is workinf value is  wrong. Create a column first and check are getting expected values

[SHS]

Hi @amitchandak 

Thank you for your quick answer. Can you please ellobare a bit more on this?

Which column do I need to create at first?

[SHS · Accepted solution]

Issue Solved. 

Though I don't exactly understand why, the RELATED Function returned a sum of the discount based on the number of instances the asset occurs in the query. So in above example, the discount is 87,4%, but the number of instances this asset occurs in the forecast table is 67, due to having 67 liferafts - so the function returned a sum of 67 * 87,4% (58,558). 

I used an AVERAGEX Function by multiplying the number of liferafts with discount, and 87,4% was returned based on this.

But is this the optimal approach? Think it is a bit silly to have 1 average measure for each discount variable (I have 4) before I can create the allocation. Is there another way to do it better?

[v-janeyg-msft]

Hi, @SHS 

The result of measure will change with the context, as will the results of the various functions used in it. 

I'm not sure if there are other workaround, because the information you provided is too little, it's all your own expression, which is not helpful to us. If you are willing to provide some sample data and your desired result, we can help you soon.

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.

Best Regards,

Community Support Team _ Janey