Forum Discussion

lbudack's avatar
lbudack
Advocate III
6 years ago

Calculate KPI Score Across Multiple Tables - Streamline

I have multiple tables bringing in KPIs, and I need to calculate their weighted monthly score as well as their overall weighted YTD score. My current method works but is very cumbersome, and I'm afra...
Need Help
v-kelly-msft's avatar
v-kelly-msft
Community Support
6 years ago

Hi lbudack ，

 

Can you show me part of your data if it's not confidential (including all tables you need to be calculated) and advise me what is your expected result.

 

Many thanks.

 

Best Regards,
Kelly

  • lbudack's avatar
    lbudack
    Advocate III
    6 years ago

    v-kelly-msft amitchandak 

     

    I'm linking a version of the .pbix file at the end of this post so you can take a look at what I have so far. I'd like to create a dashboard of gauges showing both the Month to Date and Year to Date total KPI score of all 3 of my KPI tables. (I have many more tables like this to group together.) 

     

    The columns labeled "...Score" in each KPI table are custom columns I added. They are not coming from the source. You can see the calculation I used there (it's a weighted score). Then I need to take that and add it to the other KPI Weighted Scores to get the Month to Date overall weighted score. I also need a Year to Date weighted score which I do not have done. It was working until I refreshed the data this morning with January numbers. The expected score should be 1.3 (.9+.4+0) for the Month and .9 for the (Fiscal) Year to Date. 

     

    Calculation Test File