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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
hnam_2006
Frequent Visitor

KPI Score weighted average, unrelated table

Hello

I have 6 tables in PowerBI and all of them are unrelated. I have calculated KPI scores per quarter in each of the table. Now, i need to create a overview table which gives me all 6 scores per quarter in one single view and then i want to calculate weighted average. I cannot share pbix but i have included the calculated KPI and the final expected result which i would like to see in image.

Do you know how i can approach this?KPI calculated per quarter in PowerBI desktopKPI calculated per quarter in PowerBI desktopFinal expected result shown in powerpointFinal expected result shown in powerpoint

1 ACCEPTED SOLUTION
v-kongfanf-msft
Community Support
Community Support

Hi @hnam_2006 ,

 

You can try the following steps:
1. Since your tables are not related, the first step is to create a merged table that brings together the KPI scores from all 6 tables.

ConsolidatedTable =
UNION (
    SELECTCOLUMNS (
        Table1,
        "Quarter", Table1[Quarter],
        "KPIScore", Table1[KPIScore],
        "SourceTable", "Table1"
    ),
    SELECTCOLUMNS (
        Table2,
        "Quarter", Table2[Quarter],
        "KPIScore", Table2[KPIScore],
        "SourceTable", "Table2"
    ),
    ......
)

vkongfanfmsft_0-1713518000416.png


2. Once you have the merged table, you can calculate the weighted average of the KPI scores.

WeightedAverageKPI =
SUMX (
    ConsolidatedTable,
    ConsolidatedTable[KPIScore] * ConsolidatedTable[Weight]
)
    / SUM ( ConsolidatedTable[Weight] )

If the weights are different for each table, replace the column with the actual weight. 

 

3. Finally, use the Power BI visual object to display the KPI scores and weighted averages for each quarter as required.

 

Best Regards,
Adamk Kong

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-kongfanf-msft
Community Support
Community Support

Hi @hnam_2006 ,

 

You can try the following steps:
1. Since your tables are not related, the first step is to create a merged table that brings together the KPI scores from all 6 tables.

ConsolidatedTable =
UNION (
    SELECTCOLUMNS (
        Table1,
        "Quarter", Table1[Quarter],
        "KPIScore", Table1[KPIScore],
        "SourceTable", "Table1"
    ),
    SELECTCOLUMNS (
        Table2,
        "Quarter", Table2[Quarter],
        "KPIScore", Table2[KPIScore],
        "SourceTable", "Table2"
    ),
    ......
)

vkongfanfmsft_0-1713518000416.png


2. Once you have the merged table, you can calculate the weighted average of the KPI scores.

WeightedAverageKPI =
SUMX (
    ConsolidatedTable,
    ConsolidatedTable[KPIScore] * ConsolidatedTable[Weight]
)
    / SUM ( ConsolidatedTable[Weight] )

If the weights are different for each table, replace the column with the actual weight. 

 

3. Finally, use the Power BI visual object to display the KPI scores and weighted averages for each quarter as required.

 

Best Regards,
Adamk Kong

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Fantastic. You are a life saver. Thanks. 🙂

 

Hi @hnam_2006 ,

 

Thank you for your feedback.😊

 

Best Regards,
Adamk Kong

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.