Create a Performance Score based on Monthly Total AND Yearly Average

[sg919]

Hello PBI Community!  Novice PBI/DAX user here humbly seeking assistance.

I'm trying to add a SCORING assessment for this KPI based on monthly date bins and yearly average score.

I have several KPIs I need to provide scoring assessments for, so am hoping for a scaleable solution 🙂

Any ideas appreciated.

Here is a sample of what I am receiving. I've circled SOME of the values that should be either Successful or Outstanding:

Snippet of PBI Report

Here is the measure that calculates Avg Calls / AE (Person) / Month which is referenced in the IF statement.

Avg Calls / AE / Month Calculation

WHAT I TRIED

I created a NEW TABLE (New Opportunities Created - SUMMARIZED TABLE) from the original table which contains row level detail (New Opportunities Created).

Then I created a new column to calculate SCORE:

Score = SWITCH(TRUE(),
'New Opportunities Created - SUMMARIZED TABLE'[Segment] IN {"Field Sales", "LTL"} && 'New Opportunities Created - SUMMARIZED TABLE'[#Opps]<3.5, "NI",
'New Opportunities Created - SUMMARIZED TABLE'[Segment] IN {"Field Sales", "LTL"} && 'New Opportunities Created - SUMMARIZED TABLE'[#Opps]>4.3, "Exceeds", "Meets"
)

Below is the result.

It works at the MONTHLY level, however, it is not working for the COLUMN and ROW TOTALS.

I should add that I do have some data filters as well:

AND for the FINAL piece!  I need to provide a summary at the Manager level, which is based on the TOTAL YEARLY AVERAGE ("Total #Opps" column)

[lbendlin]

Kudos for the detailed explanation. What you have in front of you is a nice introduction to filter context. Read about the different ways measures are calculated, read about aggregator functions like SUMX and then try stuff out.   One of the guiding principles in DAX  (especially for matrix visuals) is "Think like the Grand Total."  That way your measure can handle all scenarios.