Forum Discussion
Resolver ISUMX and RELATED Not Working
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.
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?
4 Replies
- amitchandak
Super User
SHS , seem like relate is workinf value is wrong. Create a column first and check are getting expected values
SHSHi 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
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?
