Results 1 to 6 of 6

Thread: Setting a Maximum value for a DAX measure

  1. #1
    Acolyte DickyMoo's Avatar
    Join Date
    Mar 2016
    Location
    London
    Posts
    27
    Articles
    0

    Setting a Maximum value for a DAX measure



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    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.
    Attached Files Attached Files

  3. #3
    Acolyte DickyMoo's Avatar
    Join Date
    Mar 2016
    Location
    London
    Posts
    27
    Articles
    0
    Thanks Brian.

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

  4. #4
    Seeker Ted Murphy's Avatar
    Join Date
    Apr 2016
    Location
    Dublin
    Posts
    11
    Articles
    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%") )

  5. #5
    Acolyte DickyMoo's Avatar
    Join Date
    Mar 2016
    Location
    London
    Posts
    27
    Articles
    0
    Very nice, I didn't know that one. Thanks Ted.

  6. #6
    Quote Originally Posted by Ted Murphy View Post
    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

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •