Reply
avatar user
Anonymous
Not applicable

Actuals vs Budgets Scorecard

Hello everone,

 

I am working on creating a Marketing Scorecard based on Actual vs Plan values by channels.

 

There are 3 channels that we need to calculate the Scorecard for.

  1. Email
  2. Broadcast
  3. DirectMail

 

There are 5 measures for which I already have the Actual and Plan values per channel per month for this year (2019):

  1. Applications
  2. Approval Rate
  3. ROA
  4. CostPerApplication
  5. Funded Dollars

 

Each measure is assigned a weightage :

MeasuresWeighting
Applications10%
Approval Rate5%
Funded Dollars30%
CostPerApplication10%
CostPerLoan20%
ROA25%

 

I need to create a Scorecard that loks like this:

 

pbiboard.png

 

 

The % Performance on the second last column above is (Actuals - Plan)/(Actuals). The Scoer on the rightmost column is based on a Score assigned to the %Performance and the below chart.

Score%Performance
5>10% of Plan
4> 5% of Plan
3Meets Plan
25% below Plan
1>10% below Plan
 25%

 

Finally, the %Weighted Score is the sumproduct of each of the Scores and the weights assigned to the measures above.

 

Could you please guide me for the same. Any help is really appreciated.

1 ACCEPTED SOLUTION
avatar user
Anonymous
Not applicable

I was able to resolve this myself. 

Created a custom matrix with the row names of 'Actual', 'Plan' and %Change' and Column names of the measures.

Then I fed the data into it using a combination of Row and Column value for each cell and it worked out.

 

Thanks!

 

View solution in original post

3 REPLIES 3
v-yuta-msft
Community Support
Community Support

@Anonymous ,

 

Could you share the sample data and give the expected result? 

 

Regards,

Jimmy Tao

avatar user
Anonymous
Not applicable

Hi Team,

 

I have already shared the expected result in the image format in my request.

 

In terms of sample data,

 

I have two tables that look something like this:

 

ActualsData ( Date column represents the end of month date for each month of year 2019)

DateChannelApplicationsApprovalRateFundedAmountAvgLoanSizeCPAROASpend
1/31/2019 Broadcast 200033% $             300,010 $           4,500 $  45 $  450 $  65,000
1/31/2019 Broadcast 230030% $             303,000 $           4,200 $  42 $  450 $  62,000
1/31/2019 Broadcast 250040% $             400,010 $           4,300 $  43 $  420 $  61,000
1/31/2019 DirectMail 276733% $             300,010 $           4,500 $  45 $  450 $  65,000
1/31/2019 DirectMail 301730% $             303,000 $           4,200 $  42 $  450 $  62,000
1/31/2019 DirectMail 326740% $             400,010 $           4,500 $  45 $  450 $  65,000
1/31/2019 Email 351733% $             300,010 $           4,200 $  42 $  450 $  62,000
1/31/2019 Email 376730% $             303,000 $           4,500 $  45 $  450 $  65,000
1/31/2019 Email 401745% $             400,010 $           4,200 $  42 $  450 $  62,000
1/31/2019 Native 426730% $             300,010 $           4,500 $  45 $  450 $  65,000
1/31/2019 Native 451745% $             303,000 $           4,500 $  45 $  450 $  65,000
1/31/2019 Native 476730% $             400,010 $           4,200 $  42 $  450 $  62,000

 

And the plan data that looks same as above but with the planned values for each of the measures.

 

Please do let me know if any other information will be helpful to proceed.

avatar user
Anonymous
Not applicable

I was able to resolve this myself. 

Created a custom matrix with the row names of 'Actual', 'Plan' and %Change' and Column names of the measures.

Then I fed the data into it using a combination of Row and Column value for each cell and it worked out.

 

Thanks!

 

avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)