sum of quantity of alternate part numbers

[aris]

Hi experts!

table A

Part nr	Alternate Part nr
A	B

Table B

P/N	q-ty
A	1
B	2

What is the formula for:

q-ty of any given P/N= q-ty of P/N + q-ty of its Alternate P/N

result: 

Q-ty of P/N A= 3

Q-ty of P/N B= 3

many thanks

[parry2k]

@aris create relation of both the columns from table a to table b, one relationship will be active and other one will be inactive, add following measures:

Part No Qty = SUM ( TableB[Qty] ) //assuming active relationship is on part number

Alternate Part No Qty =
CALCULATE ( [Part No Qty], USERELATIONSHIP ( TableA[Alternate Part No], TableB[P/N] ) )

Final Output = [Part No Qty] + [Alternate Part No Qty]

You can put all above in one measure if you want, change table and column name as per your data model.

✨ Follow us on LinkedIn and  to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

[aris]

thank you! will test it 🙂 🙂

[smpa01 · Accepted solution]

@aris  the correct answer is 3 and 2 as B does not have an alternate part no. in table1

use this measure

Measure = 
SUM ( t2[q-ty] )
    + CALCULATE (
        SUM ( t2[q-ty] ),
        TREATAS ( { MAX ( t1[Alternate Part nr] ) }, t2[P/N] )
    )

[aris]

many thanks! will test it 🙂

yes you are right, P/N B will also be listed assuming two way interchangeable

[smpa01]

@aris  once you add that row for "B" in t1, it will give you what you had in mind