Forum Discussion
Solved
How to get percentage?
Hello, I have one column as shown below. I would like to add a second column such that each row is the %age of current traffic out of the aggregate traffic. The aggregate traffic value is infact ...
See it all at work in the attached file.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ndHdToMwFAfwVznpNRn94Mu+weKNyYw3CxfMnYxmXdtQEB/fVjtlmGg0oQdyaHN+/NnvydyrEeFgX2Gw04gDycgDWqcROu0tdP6cwfbSnRBc9xyen9QRrc9gF45pZc4+HKhoKCKuTcnDjYtriS9YWquL0ZxXOefAGsma2GA5p6khCtJmCfez7dOxZC5Nf/E0cTwXkhbJI2KjlLT54iyyit++mP/d1lvnlDnBgH7SY9jy2E+Xg+mU/q/wLoIqyW6AjElKbwPzVk+jsmZFvDd21nh8j8mgTr8T3IAvCudVhGW01Gl2fS2/AFmKsKg/hKyMDSoLTtr2DQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Keyword = _t, #"SERP features" = _t, Volume = _t, KD = _t, CPC = _t, #"Previous traffic" = _t, #"Current traffic" = _t, #"Traffic change" = _t, #"Previous position" = _t, #"Current position" = _t, #"Position change" = _t, #"Previous URL inside" = _t, #"Previous URL" = _t, #"Current URL inside" = _t, #"Current URL" = _t, #"Previous date" = _t, #"Current date" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Keyword", type text}, {"SERP features", type text}, {"Volume", Int64.Type}, {"KD", Int64.Type}, {"CPC", type number}, {"Previous traffic", Int64.Type}, {"Current traffic", Int64.Type}, {"Traffic change", Int64.Type}, {"Previous position", Int64.Type}, {"Current position", Int64.Type}, {"Position change", type text}, {"Previous URL inside", type text}, {"Previous URL", type text}, {"Current URL inside", type text}, {"Current URL", type text}, {"Previous date", type datetime}, {"Current date", type datetime}}), allTraffic_ = List.Sum(#"Changed Type"[Current traffic]), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [Current traffic] / allTraffic_), #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", Percentage.Type}}) in #"Changed Type1"Please accept the solution when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
See it all at work in the attached file.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ndHdToMwFAfwVznpNRn94Mu+weKNyYw3CxfMnYxmXdtQEB/fVjtlmGg0oQdyaHN+/NnvydyrEeFgX2Gw04gDycgDWqcROu0tdP6cwfbSnRBc9xyen9QRrc9gF45pZc4+HKhoKCKuTcnDjYtriS9YWquL0ZxXOefAGsma2GA5p6khCtJmCfez7dOxZC5Nf/E0cTwXkhbJI2KjlLT54iyyit++mP/d1lvnlDnBgH7SY9jy2E+Xg+mU/q/wLoIqyW6AjElKbwPzVk+jsmZFvDd21nh8j8mgTr8T3IAvCudVhGW01Gl2fS2/AFmKsKg/hKyMDSoLTtr2DQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Keyword = _t, #"SERP features" = _t, Volume = _t, KD = _t, CPC = _t, #"Previous traffic" = _t, #"Current traffic" = _t, #"Traffic change" = _t, #"Previous position" = _t, #"Current position" = _t, #"Position change" = _t, #"Previous URL inside" = _t, #"Previous URL" = _t, #"Current URL inside" = _t, #"Current URL" = _t, #"Previous date" = _t, #"Current date" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Keyword", type text}, {"SERP features", type text}, {"Volume", Int64.Type}, {"KD", Int64.Type}, {"CPC", type number}, {"Previous traffic", Int64.Type}, {"Current traffic", Int64.Type}, {"Traffic change", Int64.Type}, {"Previous position", Int64.Type}, {"Current position", Int64.Type}, {"Position change", type text}, {"Previous URL inside", type text}, {"Previous URL", type text}, {"Current URL inside", type text}, {"Current URL", type text}, {"Previous date", type datetime}, {"Current date", type datetime}}),
allTraffic_ = List.Sum(#"Changed Type"[Current traffic]),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [Current traffic] / allTraffic_),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", Percentage.Type}})
in
#"Changed Type1"|
|
Please accept the solution when done and consider giving a thumbs up if posts are helpful. Contact me privately for support with any larger-scale BI needs, tutoring, etc. |