# Setting a Maximum value for a DAX measure

#### DickyMoo

##### New member
Hi everyone,

Is there a way to set a MAX value for a measure? I.e. the equivalent of Excel's =MIN(A1*A2),100).

I have a list of percentage variances against budget and some are massive eg 21,000%. Would be nice to show this as something like >100%, or similar.

I suspect I can do an IF('measure' > 100, ">100%", <measure>), but this might cause performance issues as I'd be doing calcs twice.

Thanks for any advice you may have.

(fyi using PowerPivot in Excel 2010)

Richard

#### Brian Smith

##### New member
It appears that it is no problem for a measure to return text when it meets a condition just like in excel. I was not able to make a measure where it would return a numerical value in one condition and text in another. There might be a way, but it seems like that would create some potential problems. Example with the text attached.

#### Attachments

• MeasuresWithCeiling.xlsx
163.2 KB · Views: 20

#### DickyMoo

##### New member
Thanks Brian.

I think I'll set anything over 100% to 100% and let users know, rather than complicate things too much.

#### Ted Murphy

##### New member
By adding a FORMAT function you can show the Actual % where it is less than your cutoff (100% in this case)

IF (Table1[BudgetToActual]>1,">100%",FORMAT([BudgetToActual],"0.00%") )

#### DickyMoo

##### New member
Very nice, I didn't know that one. Thanks Ted.

#### Brian Smith

##### New member
By adding a FORMAT function you can show the Actual % where it is less than your cutoff (100% in this case)

IF (Table1[BudgetToActual]>1,">100%",FORMAT([BudgetToActual],"0.00%") )

Nicely played :llama:

I was going to put in some clapping hands...but there's a llama. So I went with the llama. I stand by it.