Grouping by Column Value Question

[Sime]

I have the following data in a table:

Project	Contract Total	Fee Tier	Project Group
5166.135	107,850.00	>= 3M to < 5M	5166
5166.136	76,000.00	>= 3M to < 5M	5166
5166.140	220,033.00	>= 3M to < 5M	5166
5166.141	108,000.00	>= 3M to < 5M	5166
5166.143	4,500.00	>= 3M to < 5M	5166
5166.146	3,126,306.00	>= 3M to < 5M	5166
5166.148	84,500.00	>= 3M to < 5M	5166
5166.150	366,600.00	>= 3M to < 5M	5166
5166.151	45,000.00	>= 3M to < 5M	5166
5583.017	1,995,314.20	>= 1M to < 3M	5583
5583.018	100,0000.00	>= 1M to < 3M	5583
6130.002	15,000.00	< 1M	6130
6130.003	45,000.00	< 1M	6130
6166.069	-	< 1M	6166
6196.008	365,539.00	< 1M	6196
6325.000	80,747.05	< 1M	6325
6449.002	191,750.00	< 1M	6449
6451.005	0	< 1M	6451
6451.006	0	< 1M	6451
6451.009	90000	< 1M	6451

What I would like to do is to set the the Fee Tier value based on the Sum of the Contract Total for each of Project Groups.

ie: The sum of projects 5583.017 and 5583.018 = 2,095,314.20 so I would like BOTH projects lines to have a Fee Tier of >= 1M to < 3M even though project 5583.018's contract value is < 1M.

The goal being to have one Fee Tier per Project Group.

Thank you in advance.

[jgeddes]

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

[Sime]

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.

[jgeddes · Accepted solution]

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.

[Sime]

This worked fantastic.  Thank you very much for your help @jgeddes Greatly appreciated.