Forum Discussion
Conditional Column to have value '1' or '0' as per the condition given below
| Month | Year | Aggregate |
| October | 2013 | 41 |
| October | 2013 | 45 |
| October | 2013 | 33 |
| October | 2013 | 25 |
| November | 2013 | 50 |
| November | 2013 | 22 |
| November | 2013 | 53 |
| November | 2013 | 44 |
| December | 2013 | 41 |
| December | 2013 | 39 |
| December | 2013 | 35 |
| December | 2013 | 41 |
| January | 2014 | 56 |
| January | 2014 | 59 |
| January | 2014 | 41 |
| January | 2014 | 40 |
| February | 2014 | 71 |
| February | 2014 | 37 |
| February | 2014 | 33 |
| February | 2014 | 64 |
Hi all,
Above is the small part of the main table I am working on. I need a new column which gives me a '1' if [Aggregate] column's average for that month and year is greater than the [Aggregate] value of that row, else a '0'.
Could anyone please help me solve this problem?
Group by Month and Year taking the average over [Aggregate], merge this back with the original table, then write a custom column to compare the [Aggregate] and the average.
Here's a full sample query you can paste into the Advanced Editor of a new blank query:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fdCxCoAgEMbxd3FuSO9M2qOhoR4gGiwcK5AKevssp8KvSfj/9JDre9FN2zo6LzKhcknhYCmGLNV1uhOlu4r32/Vw8wt0DkAp9IIAMD9QuekLEgCVCPT/qMYuu/Vn7Hx/qgC9THc0h+M6ajf6NxgJgAwCAlCEPQ0X", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t, Year = _t, Aggregate = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type text}, {"Year", Int64.Type}, {"Aggregate", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Month", "Year"}, {{"Avg", each List.Average([Aggregate]), type nullable number}}), #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Month", "Year"}, #"Grouped Rows", {"Month", "Year"}, "Grouped Rows", JoinKind.LeftOuter), #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"Avg"}, {"Avg"}), #"Added Custom" = Table.AddColumn(#"Expanded Grouped Rows", "Custom", each if [Avg] > [Aggregate] then 1 else 0, Int64.Type) in #"Added Custom"
4 Replies
- AlexisOlson
Super User
You're more likely to get help if you provide data in a format that others can copy and paste from so we don't have to hand-type lots of data.
More tips here:
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
- AlexisOlson
Group by Month and Year taking the average over [Aggregate], merge this back with the original table, then write a custom column to compare the [Aggregate] and the average.
Here's a full sample query you can paste into the Advanced Editor of a new blank query:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fdCxCoAgEMbxd3FuSO9M2qOhoR4gGiwcK5AKevssp8KvSfj/9JDre9FN2zo6LzKhcknhYCmGLNV1uhOlu4r32/Vw8wt0DkAp9IIAMD9QuekLEgCVCPT/qMYuu/Vn7Hx/qgC9THc0h+M6ajf6NxgJgAwCAlCEPQ0X", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t, Year = _t, Aggregate = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type text}, {"Year", Int64.Type}, {"Aggregate", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Month", "Year"}, {{"Avg", each List.Average([Aggregate]), type nullable number}}), #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Month", "Year"}, #"Grouped Rows", {"Month", "Year"}, "Grouped Rows", JoinKind.LeftOuter), #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"Avg"}, {"Avg"}), #"Added Custom" = Table.AddColumn(#"Expanded Grouped Rows", "Custom", each if [Avg] > [Aggregate] then 1 else 0, Int64.Type) in #"Added Custom"