Custom Column in Power Query

[Anonymous]

Hi Experts

Kindly refer to excel file and the two columns highlighted in Blue, I am trying to re-create these two column in Power Query. See attached sample data files Excel and PBIX. i have re-created the formula in Excel, which are required in Power BI.
The fee column Always has one unique value in for each month of the year same M column M10.

Excel

https://www.dropbox.com/scl/fi/tn548lsxa7ikinqnjypry/Book1.xlsx?dl=0&rlkey=nn3nxk39d96r4wbkfkfie5fu1

Power BI

https://www.dropbox.com/s/4b1gvi2mpbouumg/TestSample_.pbix?dl=0

[Jihwan_Kim]

Hi, @Anonymous 

Please try the below measure.

Result =
VAR paydownratio =
DIVIDE (
CALCULATE ( SUMX ( TestData, TestData[Fee] ), TestData[M Column] = "M10" ),
CALCULATE ( SUMX ( TestData, TestData[Total] ), ALLSELECTED ( TestData ) )
)
VAR testtable =
SUMMARIZE (
TestData,
TestData[Year],
TestData[M Column],
TestData[Total],
"@paydown%", paydownratio
)
RETURN
SUMX ( testtable, [@paydown%] * TestData[Total] )

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster.

[Anonymous]

Hi Kim can you help with the following 
https://community.powerbi.com/t5/Desktop/Max-Value-Lookupvalue/m-p/1761194#M691153

[Jihwan_Kim]

Hi, @Anonymous 

I cannot see the file link in the post that you mentioned above.

[Vera_33 · Accepted solution]

Hi @Anonymous 

It is simply based on your sample, if your real data is more complicated, say you have different Year, you need different ways

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ldU9TgMxEIbhq0Qr0aXw+Gd+LkDHCaIUIEEHSBEU3J6daSHSvo2ljb7x2vuMnctla3J6fH25fT/ffk69ddnO21PbB+n70B5Ob5+3U7x/fexP2/X8fz6H4e14Qc7ddR0vGFkgerxg1pLkeEEuZrQ4XqD1kUCB7cOax/OeL2hgC7kWcQduSWZ/offf75ZI0Q3wkpxfBqkobSMVxT1Bf8iqVYGere4bATzEqgnJQUq8IWRViQ723etsN9CGNQrR6Fi8Z1YCHKaeOzBSUOBqoKLAB7iles7eF7kIC7yRT1WnXEEbVpOTi2qUuIKuGik+Oth4dUcHd9VIcCd/GZUFeYV5g3mH+WD52WBeYL7D/ID5CfPQd0LfCX0n9J3Qd0HfBX0X9F3Qd0HfBX0X9F3Qd0HfBX0V+ir0Veir0Fehr0Jfhb4KfRX6KvQ16GvQ16CvQV+DvgZ9Dfoa9DXoa9DXoa9DX4e+Dn0d+jr0dejr0Nehr0PfgL4BfQP6BvQN6BvQN6BvQN+AvgF9pd0Dvv4C", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year = _t, #"M Column" = _t, Total = _t, Product = _t, Fee = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", type date}, {"M Column", type text}, {"Total", Int64.Type}, {"Product", type text}, {"Fee", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "PayDown%", each List.Max(#"Changed Type"[Fee])/List.Sum(#"Changed Type"[Total])*100),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Result", each [#"PayDown%"]*[Total])
in
    #"Added Custom1"

[Anonymous]

Hi Vera, Does the above take into accout different month and Years i.e 01 Jan 2012, 01 Feb 2019 and so on for all monthly periods. 

[Vera_33]

Hi @Anonymous 

No, it only works for one year with one M10 value...if you have multiple years and indeed the similar structure, you can groupby year to get the two values first - M10 and Sum of total

[Anonymous]

Thanks Vera, Much Appericated