• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

spread for actual values against planned

B

Binas

New Member
Hi,

I have a list of activities with their monthly planned and actual values.
I'm trying to analyze the actual performance against the plan by plotting them against each other.

The catch is that distribution for planned value for next month should start only after distribution of values for first month are completed.

For e.g. in case of Activity 2-ABC ( details in attached sheet), Plan for Jan and Feb are 50 each, Actuals for Jan, Feb are 10 & 90 respectively.
So 50 out of actuals should be plotted under Jan ( until reaches planned value of 50-Jan) and remaining 50 to Feb.

The aim of this exercise is to find out if planned values were achieved, and at which points
For eg in case of month of Jan-23, planned value was 350 but it was fully achieved only in Mar ( and we have the monthly achieved values as well)

This is done on a unique activity-company level and then aggregated to get the final results
In the case of Activity 2-ABC, Actual in Feb-23 is 90, but 40 out of that is backlog from Jan-23, hence the split in Feb23 values

So as a general principle, any overage from actual less plan ( + any carry forward from previous months plan) is included as actual in the month it happened

Attached sheet with desired results and split for Jan and Feb plan

Arrangement of columns/rows are flexible and can be modified, also open to Power Query/Pivot table solutions.
 

Attachments

  • Plan Vs Actual Distribution - qn.xlsx
    35.9 KB · Views: 3
Hello Binas

What i can think of this to achive this in Excel, you can use Power Query to transform your data. Here's a simplified step-by-step guide:

1. Load your data into Power Query.
2. Create a custom column that calculates the remaining planned value for each month.
3. Use a conditional column to determine the actual value for each month, considering carry-forward from the previous month.
4. Aggregate the data as needed.

If you provide a sample data structure, I can assist you with the specific Power Query code, if needed..Try
 
Hello...I have issue in opening the file..I appreciate if you can explain the structure to help you or you can try the formula provided by changing ranges accordingly.
 
You can try this

Code: 
let
    // Assuming your data is loaded into a table named "YourTable"
    Source = YourTable,

    // Add a custom column for remaining planned value
    AddRemainingPlanned = Table.AddColumn(Source, "RemainingPlanned",
        each [Planned] - List.Sum(List.FirstN(Source[Actual], Table.PositionOf(Source[Actual], [Actual]))),
        Int64.Type),

    // Add a custom column for actual value considering carry-forward
    AddActual = Table.AddColumn(AddRemainingPlanned, "Actual",
        each if [RemainingPlanned] <= 0 then [Actual]
            else if [RemainingPlanned] >= [Actual] then [Actual]
            else [RemainingPlanned],
        Int64.Type),

    // Remove unnecessary columns
    RemoveColumns = Table.RemoveColumns(AddActual,{"RemainingPlanned"}),

    // Aggregate the data as needed
    GroupedTable = Table.Group(RemoveColumns, {"Activity", "Company", "Month"}, {{"TotalActual", each List.Sum([Actual]), Int64.Type}})
in
    GroupedTable

This code adds two custom columns, "RemainingPlanned" and "Actual," and then aggregates the data based on the specified columns. Adjust column names and types according to your actual data structure.
 
Adjusted based on sample dataset you have provided

Code: 
let
    // Assuming your data is loaded into a table named "YourTable"
    Source = YourTable,

    // Add a custom column for remaining planned value
    AddRemainingPlanned = Table.AddColumn(Source, "RemainingPlanned",
        each [PlanY] - List.Sum(List.FirstN(Source[Actual], Table.PositionOf(Source[Actual], [Actual]))),
        Int64.Type),

    // Add a custom column for actual value considering carry-forward
    AddActual = Table.AddColumn(AddRemainingPlanned, "Actual",
        each if [RemainingPlanned] <= 0 then [Actual]
            else if [RemainingPlanned] >= [Actual] then [Actual]
            else [RemainingPlanned],
        Int64.Type),

    // Remove unnecessary columns
    RemoveColumns = Table.RemoveColumns(AddActual,{"RemainingPlanned"}),

    // Aggregate the data as needed
    GroupedTable = Table.Group(RemoveColumns, {"Activity", "Company", "Month"}, {{"TotalActual", each List.Sum([Actual]), Int64.Type}})
in
    GroupedTable

This code should work with your sample data. Adjust it further based on your actual data structure if needed...Le me know
 
I really appreciate, if you let me know the error/issue you are encouraging while using the code...Give more details
 
it shows errors at the first custom column step : Expression.Error: We cannot convert a value of type List to type Table.

It would be great if you could work out the formula/code on the sample dataset
 
Check modified query!

Code: 
let
    // Assuming your data is loaded into a table named "YourTable"
    Source = YourTable,

    // Add an index column to keep track of rows
    AddIndex = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),

    // Merge the table with itself to get the cumulative sum of Actual
    MergedTable = Table.Join(AddIndex, {"Activity", "Company"}, AddIndex, {"Activity", "Company"}),

    // Filter rows to include only those where the index is less than or equal to the current row's index
    FilteredTable = Table.SelectRows(MergedTable, each [Index] >= [Index.1]),

    // Group by Activity, Company, and Month, and calculate the remaining planned value
    GroupedTable = Table.Group(FilteredTable, {"Activity", "Company", "Month"}, {{"RemainingPlanned", each List.Sum([PlanY]) - List.Sum([Actual]), Int64.Type}}),

    // Remove unnecessary columns
    RemoveColumns = Table.RemoveColumns(GroupedTable,{"Index", "Index.1"}),

    // Merge the original table with the calculated remaining planned values
    MergedTable2 = Table.Join(Source, {"Activity", "Company", "Month"}, RemoveColumns, {"Activity", "Company", "Month"}),

    // Add a custom column for actual value considering carry-forward
    AddActual = Table.AddColumn(MergedTable2, "Actual",
        each if [RemainingPlanned] <= 0 then [Actual]
            else if [RemainingPlanned] >= [Actual] then [Actual]
            else [RemainingPlanned],
        Int64.Type),

    // Remove unnecessary columns
    FinalTable = Table.RemoveColumns(AddActual,{"RemainingPlanned"})
in
    FinalTable

This code should handle the cumulative sum of Actual values and calculate the remaining planned values for each row. Adjust column names as needed based on your actual data structure.
 
Error on the third step : MergedTable

Expression.Error: A join operation cannot result in a table with duplicate column names ("Month").
Details:
[Type]
 
Here's the revised Power Query code:

Code: 
let
    // Assuming your data is loaded into a table named "YourTable"
    Source = YourTable,


    // Add an index column to keep track of rows
    AddIndex = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),


    // Merge the table with itself to get the cumulative sum of Actual
    MergedTable = Table.Join(AddIndex, {"Activity", "Company"}, AddIndex, {"Activity", "Company"}),


    // Filter rows to include only those where the index is less than or equal to the current row's index
    FilteredTable = Table.SelectRows(MergedTable, each [Index] >= [Index.1]),


    // Group by Activity, Company, and Month, and calculate the remaining planned value
    GroupedTable = Table.Group(FilteredTable, {"Activity", "Company", "Month"}, {{"RemainingPlanned", each List.Sum([PlanY]) - List.Sum([Actual]), Int64.Type}}),


    // Remove unnecessary columns
    RemoveColumns = Table.RemoveColumns(GroupedTable,{"Index", "Index.1"}),


    // Merge the original table with the calculated remaining planned values
    MergedTable2 = Table.Join(Source, {"Activity", "Company", "Month"}, RemoveColumns, {"Activity", "Company", "Month"}),


    // Add a custom column for actual value considering carry-forward
    AddActual = Table.AddColumn(MergedTable2, "Actual",
        each if [RemainingPlanned] <= 0 then [Actual]
            else if [RemainingPlanned] >= [Actual] then [Actual]
            else [RemainingPlanned],
        Int64.Type),


    // Remove unnecessary columns
    FinalTable = Table.RemoveColumns(AddActual,{"RemainingPlanned"})
in
    FinalTable

This revised code should address the duplicate column names issue. Please adjust column names as needed based on your actual data structure.
 
Replace this part of the code:

Code: 
// Merge the table with itself to get the cumulative sum of Actual
    MergedTable = Table.Join(AddIndex, {"Activity", "Company"}, AddIndex, {"Activity", "Company"}),

    // Filter rows to include only those where the index is less than or equal to the current row's index
    FilteredTable = Table.SelectRows(MergedTable, each [Index] >= [Index.1]),

With this modified step:


Code: 
// Add a custom column to calculate cumulative sum of Actual within each group
    AddCumulativeActual = Table.AddColumn(AddIndex, "CumulativeActual",
        each List.Sum(List.FirstN(AddIndex[Actual], [Index]))),


This should resolve the issue. The modified code calculates the cumulative sum of Actual within each group directly without the need for the join and filtering.
 
Hi Monty cant get it to work, getting many errors
Appreciate if you could work on the original dataset and send me the code
 
i think you are not Adjusting column names as needed based on your actual data structure. If you encounter any issues or errors, please let me know the specific error message so I can provide further assistance


Here is what you have provided
222.jpg
 
Hi Monty,

there is an error at this step :

GroupedTable = as FilteredTable is not found ( was removed in previous step)

Then next step there is error , The column "Index" of table was not found
 
You must log in or register to reply here.
Back
Top