Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Proud to be a Super User! | |
Solved! Go to Solution.
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |