Forum Discussion
Calculated Column Depends on another Calculated Column (Race condition)
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.
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 😄
5 Replies
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!
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 😄
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!
