Incorrect Total results - when to use measure or calculated column

[aashton]

I'm trying to perform what seems like simple calcuations in my Power BI and keep getting the wrong totals.  I think the confusion is when to use a measure or a calculated column.

I need to find a providers total worked hours divided by their total expected hours, over the last 4 weeks.  I start with a calculated column:

Expected Hours Per Week = IF('QGenda - Hours Worked'[Provider Type]="MD", 50, IF('QGenda - Hours Worked'[Provider Type]="CRNA", 40, 0))

I then try to find this over 4 weeks, another calculated column:

Expected Per 4 Weeks = 'QGenda - Hours Worked'[Expected Hours Per Week]*4

These are giving me the correct results. 

Total Actual Hours worked over 4 weeks is a measure:  

CALCULATE([Total Hours Worked], 'QGenda - Hours Worked'[IsInLast4FullWeeks]=1).  This is also correct. 

The issue is now finding the percentage:  Actual hours worked which is a measure divided by expected which is a column.  If I try:  

Total FTE Monthly = DIVIDE([Total Hours Worked Last 4 Weeks], SUM('QGenda - Hours Worked'[Expected Per 4 Weeks])), I get the incorrect sum for the denominator.  

How can I fix this?

[Anonymous]

Hi,

Thanks for the solution @VN999  and @Ashish_Mathur  provided, and i want to offer some more infotmation for user to refer to.

hello @aashton , based on your description, you can add allselected() function, if it cannot help you, can you provide some smaole data that can offer some more information for you.

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[VN999]

Try this below option:

Total FTE Monthly =
DIVIDE(
[Total Hours Worked Last 4 Weeks],
[Expected Per 4 Weeks],
0 // Optional: Handle division by zero
)

DIVIDE function divides Total Hours Worked Last 4 Weeks by Expected Per 4 Weeks. The optional third parameter (0) handles cases where the denominator is zero, returning 0 instead of an error.

[Ashish_Mathur]

Hi,

Share some data to work with, explain the question and show the expected result.  Share data in a format that can be pasted in an MS Excel file.