Forum Discussion
Grouping by Column Value Question
In Power Query you could add the following two lines of code...
Custom1 = Table.AddColumn(#"Changed Type", "Project Group Sum", each List.Sum(Table.SelectRows(#"Changed Type", (x)=> x[Project Group]=[Project Group])[Contract Total]), type number),
Custom2 = Table.AddColumn(Custom1, "Project Group Fee Tier", each if [Project Group Sum] >= 5000000 then ">=5M" else if [Project Group Sum] >= 3000000 then ">=3M to 5M" else if [Project Group Sum] >= 1000000 then ">=1M to 3M" else "<1M", type text)where #"Changed Type" is the previous step in your query.
Thank you very much for your recommeded solution. It has worked well in DAX and achieved the desired result.
Would the same be achievable in Power Query also?
Something like below with a modification to the filtered table I assume:
let
_groupSum =
let
projectGroup = [Project Group],
filteredTable = Table.SelectRows('Project Financial View (Grouped)', each [Project Group] = projectGroup),
totalContract = List.Sum(filteredTable[Contract Total])
in
totalContract,
_criteria =
let
groupSum = _groupSum,
criteria =
if groupSum >= 10000000 then "10M +"
else if groupSum >= 5000000 then ">= 5M to < 10M"
else if groupSum >= 3000000 then ">= 3M to < 5M"
else if groupSum >= 1000000 then ">= 1M to < 3M"
else "<1M"
in
criteria
in
_criteria
Thank you.
In Power Query you could add the following two lines of code...
Custom1 = Table.AddColumn(#"Changed Type", "Project Group Sum", each List.Sum(Table.SelectRows(#"Changed Type", (x)=> x[Project Group]=[Project Group])[Contract Total]), type number),
Custom2 = Table.AddColumn(Custom1, "Project Group Fee Tier", each if [Project Group Sum] >= 5000000 then ">=5M" else if [Project Group Sum] >= 3000000 then ">=3M to 5M" else if [Project Group Sum] >= 1000000 then ">=1M to 3M" else "<1M", type text)
where #"Changed Type" is the previous step in your query.