Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Basically, I'm trying to calculate our "capacity" which is a formula of "packets per hour target" multiplied by "hours". I want to see this my staff member but also get an accurate total (which is not showing properly in the example)
"Packets per hour target" is in a table along with staffs name (its a fixed number and does not change based on time)
"Hours" is in another table along with staff names and dates (it changes daily)
Both tables have a relationship to another table where the "staff" names and details are
The formula for capacity I have now is
Capacity = SUM('Productivity'[Labour Hours]) * SUM('Targets'[Packets per Hour])
You can see from the below, the formula is working at the name level, but not the sum.
Solved! Go to Solution.
@richardmayo
Add All(Table) as the context, because measure usually looks at the current row. Something like this:
Capacity = SUMX(ALL('Productivity'),'Productivity'[Labour Hours]) * SUMX(ALL('Target'),'Targets'[Packets per Hour])
Paul Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@richardmayo
Add All(Table) as the context, because measure usually looks at the current row. Something like this:
Capacity = SUMX(ALL('Productivity'),'Productivity'[Labour Hours]) * SUMX(ALL('Target'),'Targets'[Packets per Hour])
Paul Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hopefully a measure like this will do it.
MCapacity = SUMX(TableH, TableH[Labour Hours] * TableH[Packets per Hour])
No guarantees because I can't see your tables and relationships but you get the idea
This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907