Sum on the Row then Columns

[ToddChitt]

Seems this should be a simple exercise but it escapes me.

I have a Manpower table that lists all employees.

I have a table of Hours related to Manpower, where each employee will have many time entries.

I have a table of Rates, also related to Manpower, where each employee will have multiple Rate Types, and the SUM of each employee's Rate Type is their total Rate.

I would like to get a calculated column in the Manpower table that represents the SUM of each employee's Rate Types. A slicer on the report will allow the user to select a certain Rate Type.

If I create a Measure in the Manpower table as: Rate = SUM('Rates'[Rate]) I get a proper representation in a chart, and I can create another Measure: Amount = SUM('Hours'[Hours]) * [Rate]. But that only works at the row level. In aggregate, it is takin the (SUM of the Hours) time the (SUM of the Rate). What I am looking for is (SUBTOTAL of Hours for Employee 1) times (SUBTOTAL of Rates for Employee 1) plus the same for Employee 2, etc.

I suppose what I need is a COLUMN in the Manpower Table that is the SUM('Rates'[Rate]). But that calculation yields the total for ALL employees, not the one on the row context. SUM and SUMX give the same erroneous results.

Thanks in advance

[Eric_Zhang · Accepted solution]

@ToddChitt

You can try a measure as below. See more in the attached pbix file.

Measure =
VAR sumizedTble =
    SUMMARIZE (
        Manpower,
        Manpower[Depart],
        Manpower[EmpID],
        "subtotalHours", SUM ( Hours[Hours] ),
        "subtotalRate", SUM ( Rates[rate] )
    )
RETURN
    SUMX ( sumizedTble, [subtotalHours] * [subtotalRate] )

3*1.1+5*0.5=5.8