Calculated Column Depends on another Calculated Column (Race condition)

[Anonymous]

Hi 

I have the following Tables:

Order table

Part Number	Price	Quantity	Seller Scalar	Total Scalar
Part 1	10	2	0.8	0.8
Part 1	12	4	1	1
Part 2	5	2	1	1

Part Numbers table

Part Number	Total Scalar
Part 1	1.8
Part 2	1

I want to use this information to calculate a normalized scalar, so I can make a Weighted Average Price.

At the moment I do it by duplicating the Order table and adding a calculated Column Normalized Scalar.

New table

Part Number	Price	Quantity	Seller Scalar	Total Scalar	Normalized Scalar
Part 1	10	2	0.8	0.8	0.8/1.8
Part 1	12	4	1	1	1/1.8
Part 2	5	2	1	1	1/1

The problem is that the New table does not look like the one above, but more like the one below.

Part Number	Price	Quantity	Seller Scalar	Total Scalar	Normalized Scalar
Part 1	10	2	0.8	0.8
Part 1	12	4	1	1
Part 2	5	2	1	1

All the values become empty because Seller Scalar divided by what I assume is an empty value equals nothing.

I assume this is because the New table is calculated first followed by the Part Numbers table.

This means that when the Normalized Scalar is calculated the values needed from Total Scalar in Part Numbers has yet to be calculated. 

I think this would be solved by having Orders be calculated first followed by Part Numbers and at last a New table with Normalized Scalars. 

However, I don't know how to control when a table is calculated in relation to all other tables.  

Thank you for your time.

[parry2k]

@Anonymous assuming you have a relationship between these tables and part number table is on one side of the relationship, you can add new column like this

New Scalar Column = 
DIVIDE ( Order[Scalar Value], RELATED( PartTable[Total Scalar] ) ) 

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

[Anonymous · Accepted solution]

Hi @parry2k 

Your reply was not the answer but it did lead me to it.

The problem was some of my Relations were way off, so I think Power BI couldn't figure out what was what. 

But anyway thanks for pointing in the right direction 😄 

[dax]

Hi @Anonymous , 

It seems that there is no relationship between two tables, right? You could try to refer to below expression to create calculated column

Column = CALCULATE(SUM('Order table'[Seller Scalar]))/MINX(FILTER('Part Numbers','Part Numbers'[Part Number]=EARLIER('Order table'[Part Number])),'Part Numbers'[Total Scalar])

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[parry2k]

@Anonymous glad to hear that issue is resolved, that's what matters. cheers!

[parry2k]

@Anonymous glad it worked out, it is always tough to reply when looking at the problem because sometimes you don't have the full picture. Anyhow, the goal is to get you over the line and which seems like to be the case. Cheers!!

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!