Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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 |
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:
Create a new table called HS_Weights with two columns: HS_property and HS_weight.
HS_property HS_weightHS property_A | 4 |
HS property_B | 5 |
... | ... |
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.
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:
In TABLE_A, create a new calculated column named "Weighted_HS."
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:
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.
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:
It calculates the total weight by summing up all the weights in TABLE_B.
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."
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.
It sums up these weighted values using the SUMX function.
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?
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
86 | |
84 | |
83 | |
67 | |
49 |
User | Count |
---|---|
131 | |
111 | |
96 | |
71 | |
67 |