Corrective Action

[Bking5]

I am trying to solve for a corrective action request inside a Power BI report.   I have added a screenshot of the report.  The basis of the corrective action is to make sure that sales are being hit each month.   The criteria for the corrective action is:
1. If the advisor doesn't hit goal for Cash sales in the month, total sales in the month, Cash Sale per hour, Total Sales per hour, but they do hit the YTD total for Cash sales and YTD Total Sales then they get - Training Refresh

2.  The next month if they do the same thing then the corrective moves up a notch to a Coaching Level 1 -Written coaching
3.  if the advisor misses 3 months then it moves to a Level 2- Performance Improvement Plan to be resolved in 30 days
4.  if the advisor misses again then it moves to a Level 3 -Final Corrective Action issues for 30 days
5.  if the advisor still misses then the final stage is Termination

6.  The caveat to each of these is if they complete the task of any of them then it resets back to the beginning.

Any help or advice in building this out is greatly appreciated. 

Regards,

Bking5

[Bking5]

I have tried to add a pbix file as I stated above but I get this error.  Is there a different way to add a pbix file other than drag and drop that maybe I'm missing?  

[Bking5]

I have tried to add the file here but it says "The file type (.pbix) is not supported".  I am needing to be able to create a "Notes" field or measure that can create a corrective action status for each month.   The criteria is based off the data below.  4 of the areas data is based off columns and 2 are based off measures. 
Cash Sales
Cash Sales Per Hour
Total Sales
Total Sales per Hour
Cash Sales YTD % to Goal
Total Sales YTD % to Goal.

1. If the advisor doesn't hit goal for Cash sales in the month, total sales in the month, Cash Sale per hour, Total Sales per hour, but they do hit the YTD total for Cash sales and YTD Total Sales then they get - Training Refresh

2.  The next month if they do the same thing then the corrective moves up a notch to a Coaching Level 1 -Written coaching
3.  if the advisor misses 3 months in a row then it moves to a Level 2- Performance Improvement Plan to be resolved in 30 days
4.  if the advisor misses again for the 4th month in a row then it moves to a Level 3 -Final Corrective Action issues for 30 days
5.  if the advisor still misses for 5 months in a row then the final stage is Termination

6.  The caveat to each of these is if they complete the task requirements of any of them then it resets back to the beginning where no corrective is assigned.

Measures used on report:

Cash Sales YTD = CALCULATE(TOTALYTD(sum('Travel Incentive/Scorecard'[Cash Sales]),'Calendar'[Date]))

Cash Sales YTD % to Goal = DIVIDE('Travel Incentive/Scorecard'[Total Cash Sales YTD],'Travel Incentive/Scorecard'[Total Cash YTD Sales Goal])

Cash SPH Goal = CALCULATE(SUM('Travel Incentive/Scorecard'[Agent Cash Goal])/[Hours Projected worked])

% to Cash Goal = CALCULATE(Sum('Travel Incentive/Scorecard'[Cash Sales]))/calculate(Sum('Travel Incentive/Scorecard'[Agent Cash Goal]))

% to Total Goal = CALCULATE(Sum('Travel Incentive/Scorecard'[Total Sales])/Sum('Travel Incentive/Scorecard'[Agent Total Goal]))

Total Cash YTD Sales Goal = CALCULATE(TOTALYTD(SUM('Travel Incentive/Scorecard'[Agent Cash Goal]),'Calendar'[Date]))

Total Sales YTD = CALCULATE(TOTALYTD(sum('Travel Incentive/Scorecard'[Total Sales]),'Calendar'[Date]))

Total Sales YTD % to Goal = DIVIDE('Travel Incentive/Scorecard'[Total Sales YTD],'Travel Incentive/Scorecard'[Total YTD Sales Goal])

Total SPH Goal = CALCULATE(SUM('Travel Incentive/Scorecard'[Agent Total Goal])/[Hours Projected worked])

Total YTD Sales Goal = CALCULATE(TOTALYTD(SUM('Travel Incentive/Scorecard'[Agent Total Goal]),'Calendar'[Date]))

fields created:

Agent Cash Goal = CALCULATE(Sum('Travel Goals'[Cash Sales Goal]), Filter('Travel Goals', 'Travel Goals'[Sub-Application] = EARLIER('Travel Incentive/Scorecard'[Sub Application]) && 'Travel Goals'[Application] = EARLIER('Travel Incentive/Scorecard'[Application]) && ('Travel Goals'[MM-YYYY]) = EARLIER('Travel Incentive/Scorecard'[MM-YYYY])))

Agent Total Goal = CALCULATE(Sum('Travel Goals'[Total Sales Goal]), Filter('Travel Goals', 'Travel Goals'[Sub-Application] = EARLIER('Travel Incentive/Scorecard'[Sub Application]) && 'Travel Goals'[Application] = EARLIER('Travel Incentive/Scorecard'[Application]) && 'Travel Goals'[MM-YYYY] = EARLIER('Travel Incentive/Scorecard'[MM-YYYY])))

[v-yetao1-msft]

Hi @Bking5 

Can you provide a sample or pbix file(remove sensitive info) to describe your need in detail ?

Best Regards,
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.