Forum Discussion

rm82's avatar
rm82
New Member
8 years ago

Limit Formula to Maximum Value

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

 

 

2 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    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