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.
Something like the following calculated column might work for you...
_ProjectGroupFee Tier =
var _groupSum =
//calculate the Contract Total for the selected Project Group
CALCULATE(
SUM(Query1[Contract Total]),
ALLEXCEPT(Query1, Query1[Project Group]) //filters to all of Query1 table where the Project Group = the Project Group in current row
)
var _criteria =
//tests the group sum in the order listed
SWITCH(
TRUE(),
_groupSum >= 5000000, ">=5M",
_groupSum >= 3000000, ">=3M to <5M",
_groupSum >= 1000000, ">=1M to <3M",
"<1M"
)
Return
//returns the criteria
_criteria
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.