Help with DAX formula to calculate Resource Hours based on Capacity, Resource Value, and Res_count

[Saini_U]

Hi everyone,

I’m working on a Power BI model with three related tables:

1. UserCapacity → contains total User Capacity (e.g., 4  per project)

2. MainTable → contains resource values (e.g., 0.25 like 25% occupy user for per project)

3. ProjectResources → contains the resource users for each project (e.g., 2 users)

4.  ProjectHR→ contains the Total Hourse for each project (e.g., 900 hr per project)
   
   Now i want to apply below formula :
   900 * 0.25 = 225 / 4 = 57 
   
   Output in stack column chart :- 
   | ProjectID | Resource | Capacity| Resource Value | Hours | project |  final result 
   | --------- | -------- | -------------- | -------------- | ----- | ---------------------------- |
   | 1 | BDLead | 4  | 0.25 | 900 | project 1  |  57 
   
   
   

   I’ve been trying multiple DAX approaches, and I’m facing an issue with my calculation.

   I’m able to get the correct value for the expression 900 * 0.25, but when I divide it by capacity (using any aggregation like SUM, MAX, etc.), the output becomes incorrect.

   I already have individual values for each project and resource, so I don’t want to use any aggregation functions such as SUM, MAX, or AVERAGE, because they cause the result to roll up incorrectly.

   Could someone please help me fix this issue or suggest a better way to handle this calculation in DAX?

    
   Thanks in advance for your help!

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[saritasw]

Hi @Saini_U ,

Try using SELECTEDVALUE() to force Power BI to use the exact value from the row.
DAX :

Final Hours =
VAR ProjectHours = SELECTEDVALUE(ProjectHR[Hours])
VAR ResourceValue     = SELECTEDVALUE(MainTable[ResourceValue])
VAR Capacity     = SELECTEDVALUE(UserCapacity[Capacity])
RETURN
ProjectHours * ResourceValue / Capacity

 If your tables are connected correctly in a 1-to-many direction, Power BI knows exactly which Capacity, Resource Value, and Project Hours belong to each row.
So the DAX formula picks the right single value for each project and resource, and the result becomes accurate.

******************************************************************************************************************
If this solution worked for you, kindly mark it as Accept as Solution. This would be helpful for other members who may encounter similar issues and feel free to give a Kudos, it would be much appreciated!

Thank you,
Sarita