Setting a Maximum value for a DAX measure

DickyMoo

New member
Joined
Mar 7, 2016
Messages
27
Reaction score
0
Points
0
Location
London
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
Joined
Apr 1, 2016
Messages
57
Reaction score
0
Points
0
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
Joined
Mar 7, 2016
Messages
27
Reaction score
0
Points
0
Location
London
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
Joined
Apr 14, 2016
Messages
11
Reaction score
0
Points
0
Location
Dublin
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%") )
 

Brian Smith

New member
Joined
Apr 1, 2016
Messages
57
Reaction score
0
Points
0
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.
 
Top