Column to Sum data based on another column

[Anonymous]

Hello everyone,

First off I am new to Power BI so I apoligize if this is an easy answer. I tried googling and really could not come up with what I am looking for.

I need too add a Column to my Budget table called "PBL Total Salary" to display the sum of "Total Budgeted Salary" for each "Position Number"

All of this is coming from the same table.

Thank you in advance!

Example:

Position Number	Total Budgeted Salary	PBL Total Salary
00072481	9,326.72	15,808.00
00072481	5,216.64	15,808.00
00072481	1,264.64	15,808.00
00005962	7,752.84	13,140.40
00005962	4,336.33	13,140.40
00005962	1,051.23	13,140.40
00003291	24,803.25	42,039.40
00003291	13,873.00	42,039.40
00003291	3,363.15	42,039.40

[edhans · Accepted solution]

Here you go:

Total Budget Salary = 
VAR PositionNo = [Position Number]
RETURN
SUMX(
    FILTER(
        Budget,
        [Position Number] = PositionNo
    ),
    [Total Budgeted Salary]
)

the PostionNo Variable captures the current position number, then the FILTER statement compares that to the current row it is on in the table. It moves through each row one at a time.

[Anonymous]

Thank you! Works perfect 😀

[edhans]

Great @Anonymous . Glad to help.

[Anonymous]

Hi again, I am trying to do this again for a different report and I am getting an error, the column 'Department ID' does exist in the Budget table so I am really confused by this 

I am trying to accomplish this:

Department ID	Operating FTEs	Total Operating FTEs
221345	1.0	1.5
221345	0.5	1.5
221356	1.0	2.0
221356	1.0	2.0
221789	1.0	2.5
221789	1.0	2.5
221789	0.5	2.5

@edhans 

[edhans]

You have to specify the table, then use MAX to convert it to a scalar value.

VAR DeptID = MAX(TableName[Department ID])

[Anonymous]

You're a life saver!