SumProduct Between two Table Values

[ashicool_10061]

Hi There,

I am trying to replaicate one of the SUMPRODUCT calculation in power bi dekstop.

I have two tables Table 1 and Table 2 . In Table 2 i have 3 columns Overall, EN abd Fr which are calculated measures created based on calculations.

So my requirement is now We need to do Sumproduct of these columns with Volume (column)present in Table 1 .

Please see below screenshot and attached excel for better understandig:

Formula for EN=

EN_ = DIVIDE(
    SUMX(FILTER('VQ''s',CONTAINSSTRINGEXACT('VQ''s'[VQ's],"EN")),
    'VQ''s'[SL%]*'VQ''s'[CaLL_Answered]),SUMX(FILTER('VQ''s',CONTAINSSTRINGEXACT('VQ''s'[VQ's],"EN")),'VQ''s'[CaLL_Answered]),0)

FR_ = ROUND(DIVIDE(
    SUMX(FILTER('VQ''s',CONTAINSSTRINGEXACT('VQ''s'[VQ's],"FR")),
 'VQ''s'[SL%]*'VQ''s'[CaLL_Answered]),SUMX(FILTER('VQ''s',CONTAINSSTRINGEXACT('VQ''s'[VQ's],"FR")),'VQ''s'[CaLL_Answered]),0),2)

Need to create SuMproduct(EN * Volume)/Sum(Volume)  for Final Table:

Request to please help me in creating this .

Thanks,

Ash

@Ashish_Mathur @amitchandak 

[Kedar_Pande]

@ashicool_10061 

Create a measures

Total Volume = SUM('Table1'[Volume])

EN_F_Final = 
DIVIDE(
SUMX(
'Table1',
'Table2'[EN_] * 'Table1'[Volume]
),
[Total Volume],
0
)

You can now use the newly created EN_F_Final measure in your report visuals. Place your LOB's as rows and this measure as a value, and you should see the results reflecting your SUMPRODUCT logic.

You can create a similar measure for FR and any other metrics you need to compute averages

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[ashicool_10061]

Hi @Kedar_Pande ,

i will try this as well. Appreicate your resposne.

Reagrds,

Ash

[FarhanJeelani]

Hi @ashicool_10061 

To replicate the `SUMPRODUCT` calculation from the screenshot in Power BI using your tables, follow these steps:

 1. Understand the Requirement:
- Inputs:
- `Volume` column from Table 1.
- Calculated measures (`EN` and `FR`) from Table 2.
- Formula:

SUMPRODUCT(EN * Volume) / SUM(Volume)

- This means you want to multiply `Volume` by `EN` (or `FR`), sum the products, and divide by the sum of `Volume`.

2. Steps in Power BI:

Step 1: Ensure Relationships Exist
- Confirm that Table 1 and Table 2 are connected by a relationship using the `LOB` column (as shown in the screenshot). Ensure the relationship is one-to-many, with Table 1 on the "many" side.

Step 2: Create Measures

For EN Final Measure:
This measure calculates the weighted average using `EN`:

DAX

EN_Final =
DIVIDE(
SUMX(
'Table 1',
'Table 1'[Volume] * RELATED('Table 2'[EN])
),
SUM('Table 1'[Volume]),
0
)

For FR Final Measure:
This measure calculates the weighted average using `FR`:
DAX

FR_Final =
DIVIDE(
SUMX(
'Table 1',
'Table 1'[Volume] * RELATED('Table 2'[FR])
),
SUM('Table 1'[Volume]),
0
)

3. Explanation of DAX Logic:
1. `SUMX`:
Iterates through each row in `Table 1`, multiplying `Volume` by the corresponding value of `EN` or `FR` from `Table 2`.

2. `RELATED`:
Fetches the matching `EN` or `FR` value from Table 2 using the relationship.

3. `DIVIDE`:
Divides the weighted sum of `EN` (or `FR`) by the total `Volume` to calculate the weighted average. The third argument (`0`) handles division by zero gracefully.

4. Result Table in Power BI:
To display the results:
- Add a table visual.
- Include the `LOB` column from Table 1 and the newly created measures (`EN_Final` and `FR_Final`).

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

If you need further help implementing this or have additional requirements, let me know! 😊

[ashicool_10061]

Hi @FarhanJeelani ,

Thank you for this explanation.

I am trying to create the same formula  but it snot picking up the Related Table .(Table 1 is connected with Table 2 where table 2 is of Many side and Table1 is at 1 side)

Please let me know where i am doing wrong ?

Thanks,

Ash

[FarhanJeelani · Accepted solution]

Hi @ashicool_10061 ,

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.

[ashicool_10061]

Hi @FarhanJeelani ,

Thank you for sucha breif explantion and it works .

Regards,

Ash

[danextian]

If you aren't a superuser, attaching files is disabled. You can however post a link to a cloud storage.