Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
ebo01
Regular Visitor

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

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_emailHS property_AHS property_BWeighted_HS
anna@user.com10 
edvin@user.com0,60,5 


TABLE_B

HS_propertyHS_weight
HS property_A4
HS property_B5

 

5 REPLIES 5
123abc
Community Champion
Community Champion

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_A4
    HS property_B5
    ......
  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.

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
Community Champion
Community Champion

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
Community Champion
Community Champion

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.

 

 

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?

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.