Limit Formula to Maximum Value

[rm82]

Hello everyone,

I am having an issue with a calculated field. 

We have a row that may sum up several numbers. I entered Internal time today for the following amounts:

2

9

5

5

I have a formula created that looks like this.

Internal = CALCULATE(min(6,sum(Time_Entry[Hours_Actual])), v_rpt_Time[WORK_TYPE] = "Internal")

This formula works except it does not apply to the sum of the lines, but each individual line. So it will exclude the line with "9", but all the other lines are under "6" so it add them up to a total of "12".

I need it to have formula Sum and then apply the MIN. Any help?

Thanks!

rm82

[v-jiascu-msft]

Hi @rm82,

Can you mark the proper answer as a solution please?

Best Regards,

Dale

[v-jiascu-msft]

Hi @rm82,

Seems you need to remove the filter of that column. Try something like below. If it doesn't work, please provide a more complete sample.

Internal =
CALCULATE (
    MIN ( 6, SUM ( Time_Entry[Hours_Actual] ) ),
    v_rpt_Time[WORK_TYPE] = "Internal",
    ALL ( Time_Entry )
)

Best Regards,

Dale