SumProduct Between two Table Values

Hi Anonymous ,

Can you please attach the link to doanload the pbix fle?

If the measures you created are still not working and the `RELATED` function is not pulling values as expected, this could indicate an issue with your data model or how the relationship between the tables is defined. Here's how to troubleshoot and fix the problem:

---

 1. Check the Relationships
- Open the **Model View** in Power BI Desktop and ensure:
- There is a 1-to-Many (1:*N*) relationship between Table 1 (one side) and Table 2 (many side).
- The relationship is active.
- The correct columns are mapped (e.g., `LOB` or similar key).

---

2. Ensure Correct Use of `RELATED`
- The `RELATED` function works only when:
- You're referencing a column from the related (one-side) table (e.g., Table 2).
- The relationship flows from Table 1 to Table 2.

If this doesn't work, you may need to adjust the relationship direction or use `TREATAS`.

---

3. Alternative to `RELATED`: Use `SUMMARIZE` or `LOOKUPVALUE`
If `RELATED` isn't picking up values due to relationship direction or data issues, you can try these alternatives:

Using `SUMMARIZE`:
DAX

EN_Final =
DIVIDE(
SUMX(
SUMMARIZE(
Table1,
Table1[LOB],
"Volume", Table1[Volume],
"EN", MAX(Table2[EN])
),
[Volume] * [EN]
),
SUM(Table1[Volume]),
0
)

 Using `LOOKUPVALUE`:

DAX
EN_Final =
DIVIDE(
SUMX(
Table1,
Table1[Volume] * LOOKUPVALUE(Table2[EN], Table2[LOB], Table1[LOB])
),
SUM(Table1[Volume]),
0
)

---

 4. Use a Measure Instead of Calculated Columns:
Ensure you create these formulas as measures, not calculated columns, because:
- Measures are context-sensitive and update dynamically with visuals.
- Calculated columns evaluate only once during the dataset load.

---

5. Double-Check the Data Types
- Ensure that the `LOB` column in both tables has the same data type (e.g., `Text` or `Integer`).
- A mismatch can cause the relationship or `RELATED` function to fail.

---

6. Verify Your Calculated Measures
Ensure that the `EN` and `FR` calculated measures are correctly returning values in Table 2. You can test this by creating simple table visuals for Table 2 and displaying `EN` and `FR`.

---

7. Full Correct DAX Example
If the relationships are properly configured, this DAX measure should work:
DAX

EN_Final =
DIVIDE(
SUMX(
Table1,
Table1[Volume] * RELATED(Table2[EN])
),
SUM(Table1[Volume]),
0
)

---

8. Test the Measures in a Visual
- Add a table visual in Power BI.
- Include the `LOB` column from Table 1 and the new measures (`EN_Final`, `FR_Final`).
- Check if the calculations match your Excel results.

---

If these steps still don't resolve the issue, let me know, and I can help further troubleshoot by refining the DAX or addressing specific errors!

Please mark this as solution if it helps. Appreciate Kudos.