New column in TABLE A that multiply with corresponding values from TABLE B, transposed

[Anonymous]

I have 2 tables showing data to do with user health scores.

I want to create a weighted health score where i use the weight from TABLE_B in the column calculation for TABLE_A.
How would that calculation look like?

In plain text:
Take the value from HS property in Table A, multiply it with the corresponding weight in Table B.
Then after all properties are multiplied with their corresponding weight, sum them up and divide by the total sum of weights.
Display the result in TABLE A column "Weighted_HS".

TABLE_A

User_email	HS property_A	HS property_B	Weighted_HS
anna@user.com	1	0
edvin@user.com	0,6	0,5

TABLE_B

HS_property	HS_weight
HS property_A	4
HS property_B	5

[123abc]

If you are working in Power BI or a similar tool where you need to set up relationships between tables, you're on the right track. To set up relationships when one table's column titles (properties) are related to row values in another table, you can create a bridge table or use a DAX formula to establish the relationship.

In your case, since you have 15 different health score (HS) properties, it would be efficient to create a bridge table that maps the properties to their corresponding weights. Here's how you can do it:

1. Create a new table called HS_Weights with two columns: HS_property and HS_weight.

   HS_property HS_weight

   HS property_A	4
   HS property_B	5
   ...	...

2. Create relationships between TABLE_A and HS_Weights and TABLE_B and HS_Weights using the HS_property column.

Now, you can use DAX to calculate the weighted health score in Power BI. Here's a DAX formula to calculate the weighted health score:

Weighted_HS =
VAR TotalWeight = SUMX(HS_Weights, [HS_weight])
RETURN
SUMX(RELATEDTABLE(HS_Weights), [HS_weight] * RELATED(TABLE_A[HS property_A])) / TotalWeight

This DAX formula calculates the weighted health score by summing the product of each HS property's weight and the corresponding value from TABLE_A for all properties, and then dividing it by the total weight.

Make sure to adjust the DAX formula and table names according to your specific dataset and Power BI setup.

[Anonymous]

The new table that you propose I create is the exact same table as TABLE B, so I am not sure how to move forward. 

[123abc]

I see that TABLE_B and the proposed reshaped table in my previous response have a similar structure. If TABLE_B contains the HS properties and their corresponding weights, and you want to use these weights to calculate the weighted health score for each user in TABLE_A, you don't necessarily need to create a new table. You can work with TABLE_A and TABLE_B directly.

Here's how you can calculate the weighted health score for each user in TABLE_A based on the weights from TABLE_B, without creating a new table:

1. In TABLE_A, create a new calculated column named "Weighted_HS."

2. Use the following DAX formula for the "Weighted_HS" column:

Weighted_HS = [HS property_A] * RELATED(TABLE_B[HS_weight]) + [HS property_B] * RELATED(TABLE_B[HS_weight])

In this formula:

• [HS property_A] and [HS property_B] refer to the columns in TABLE_A that contain health scores.
• RELATED(TABLE_B[HS_weight]) retrieves the related weight from TABLE_B based on the HS property in the current row of TABLE_A.

1. Now, you should have the "Weighted_HS" column in TABLE_A with the calculated weighted health scores for each user.

This approach assumes that you have a proper relationship between TABLE_A and TABLE_B based on a common key, which could be the user email or another unique identifier. If the relationship is not established, you might need to ensure that a relationship is defined between the tables based on the appropriate key before using the RELATED function.

This way, you can calculate the weighted health scores for each user in TABLE_A using the weights from TABLE_B without creating an additional table.

[123abc]

To calculate the weighted health score for TABLE_A based on the weights from TABLE_B, you can use the following DAX formula to create a new calculated column in TABLE_A called "Weighted_HS":

Weighted_HS = VAR TotalWeight = SUMX(RELATEDTABLE(TABLE_B), TABLE_B[HS_weight]) RETURN DIVIDE( SUMX( FILTER(TABLE_B, TABLE_B[HS_property] IN { "HS property_A", "HS property_B" }), RELATED(TABLE_A[HS property_A]) * TABLE_B[HS_weight] ), TotalWeight )

This formula does the following:

1. It calculates the total weight by summing up all the weights in TABLE_B.

2. It then uses the FILTER function to iterate through TABLE_B and filter only the rows where the HS_property is either "HS property_A" or "HS property_B."

3. For each of those rows, it multiplies the corresponding health score value (either HS property_A or HS property_B) from TABLE_A with the weight from TABLE_B.

4. It sums up these weighted values using the SUMX function.

5. Finally, it divides the sum of weighted values by the total weight to calculate the weighted health score and assigns it to the "Weighted_HS" column in TABLE_A.

After adding this calculated column to TABLE_A, it will display the weighted health score based on the weights from TABLE_B for each user.

[Anonymous]

Thankyou for the quick reply!
I am having some issues on the final part of the query:
RELATED(TABLE_A[HS property_A]) * TABLE_B[HS_weight] )

I think it is because the tables relationship is not set up yet. I wasnt sure how to do it when ones column's titles are related to row values in another? I have only done this b4 when column relates to column.

I have probably around 15 different HS properties, how would that look like in the part of the query posted above?