Calculate KPI Score Across Multiple Tables - Streamline

[lbudack]

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 afraid it will bog down my report. 

In each table, I have multiplied the KPI by the weight to get the overall score. Then I use measures to calculate the MTD total for that KPI and then add it together with the others in that category to get a monthly total for the category. (My categories are in another table with an inactive relationship to an ID number in each table.) Is there an easier way to do this? I'm sure there is, but I'm not experienced enough in DAX to write an expression organically to solve this. 

Steps of my current set up are below: 

1.)    Calculated column of KPI Score is: BSC_ConstructionSubmittalsBSCKPI[kpiWeight]*BSC_ConstructionSubmittalsBSCKPI[Submittals KPI]

Calculated column of KPI Score is: 

BSC_ConstructionSubmittalsBSCKPI[kpiWeight]*BSC_ConstructionSubmittalsBSCKPI[Submittals KPI]

2.)  Then I take that column and use a quick measure to get the MTD value as follows: 

IF(
ISFILTERED('BSC_ConstructionSubmittalsBSCKPI'[moStart]),
TOTALMTD(
SUM('BSC_ConstructionSubmittalsBSCKPI'[Submittal KPI Score]),
'BSC_ConstructionSubmittalsBSCKPI'[moStart].[Date]
)
)

3.)  THEN I add it to others in its category with the measure below: 

[CO KPI Score MTD] + [Gates KPI Score MTD] + [Submittal KPI Score MTD]

I wanted to see about solving this before I move on to calculating their YTD score which I would also be grateful for help on. Thanks! 

[v-kelly-msft]

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]

@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 

[amitchandak]

The information you have provided is not making the problem clear to me. Can you please explain with an example. If possible please share a sample pbix file after removing sensitive information.
Thanks.

My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...