Ratio % of a total column by week

[Anonymous]

Hi,

I am looking the make the below calculation, but I am going around in circles!:

'% Complete' * 'Current Forecast Cost CFC' Total

Example with data:

for 'Activity Management' on 07/07/2019 = 60% of £10,457.50, and then for the same row on the 14/07/2019 = 62% of £10,457.50

The issue is that when this is then split down to the weeks it is taking the forecast for the week and not the 'Current Forecast Cost CFC' Total.

All fields are on the same table.

If someone could provide some guidance here it would be appreciated!

[v-shex-msft]

HI @Anonymous ,

Please share pbix file with sample data and expected result, it is hard to test/coding formula without sample data.

How to Get Your Question Answered Quickly

Regards,

Xiaoxin Sheng

[Anonymous]

Hi @v-shex-msft 

Thank you for the quick reply.

I have attached an excel document with the desired calcuation below:

https://1drv.ms/x/s!Auc9GH0QH8WIjxhkdeK4569IpAPf?e=JWeeGw

In summary the calculation is below:

EVM = Current Forecast Budget CFC (TOTAL) * % Complete

Thanks for the help!

[Anonymous]

Hi @v-shex-msft , is there any chance you could help with this one?

Greatly appreciated if you could 

Thanks

[v-shex-msft · Accepted solution]

Hi @Anonymous ,

I try to convert your query tables structure and do unpivot columns on these records and fields, then you can use converted table fields to create a matrix who similar as excel worksheet pivot table.

let
    Source = Excel.Workbook(File.Contents("C:\Users\xxxxxxx\xxxxxxx\EVM Calculcation.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", type text}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}, {"Column11", type any}, {"Column12", type any}, {"Column13", type any}, {"Column14", type any}, {"Column15", type any}, {"Column16", type any}, {"Column17", type any}, {"Column18", type any}, {"Column19", type any}, {"Column20", type any}, {"Column21", type any}, {"Column22", type any}, {"Column23", type any}, {"Column24", type any}, {"Column25", type any}, {"Column26", type any}}),
    #"Removed Bottom Rows" = Table.RemoveLastN(#"Changed Type",2),
    #"Removed Columns" = Table.RemoveColumns(#"Removed Bottom Rows",{"Column1", "Column2"}),
    #"Transposed Table" = Table.Transpose(#"Removed Columns"),
    #"Filled Down" = Table.FillDown(#"Transposed Table",{"Column1"}),
    #"Grouped Rows" = Table.Group(#"Filled Down", {"Column1"}, {{"Contents", each Table.AddIndexColumn(Table.PromoteHeaders(Table.Transpose(Table.RemoveColumns(_,"Column1")), [PromoteAllScalars=true]),"Index",1,1), type table}}),
    #"Expanded Contents" = Table.ExpandTableColumn(#"Grouped Rows", "Contents", {"% Complete", "EVM", "Index"}, {"% Complete", "EVM", "Index"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Contents", "Custom", each Table.SelectColumns(#"Removed Bottom Rows",{"Column1","Column2"}){[Index]+1}),
    #"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"Column1", "Column2"}, {"Task Name", "Current Forecast Budget CFC (TOTAL)"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded Custom",{"Index"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"Task Name", "Current Forecast Budget CFC (TOTAL)", "Column1", "% Complete", "EVM"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Reordered Columns", {"Task Name", "Current Forecast Budget CFC (TOTAL)", "Column1"}, "Attribute", "Value")
in
    #"Unpivoted Columns"

Regards,

Xiaoxin Sheng