none
Tabular measure format string RRS feed

  • Question

  • Hi

    I have created measure under SSAS tabular and wanted to show measure as percent and another to whole number instead of decimal. I tried setting Format string as Whole number but that did not worked.

    Monday, April 9, 2018 5:25 PM

Answers

  • Hi Arnav1210,

    Thanks for your question.

    If you want to show measure as percent,you might want to set format to "Percentage". If you are using SSAS tabular model with compatibility 1400, you can also set format to custom, format string to '0.00%;-0.00%;'

    For measures shown as whole number,  you might want to set format as whole number, set Decimal places to from 2 to blank as above image.

    The measure format string might also affected by the client tool as Darren mentioned, thus you may need to take care of that also.


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by Arnav1210 Wednesday, April 11, 2018 5:17 PM
    Tuesday, April 10, 2018 4:22 AM
    Moderator
  • Hi Arnav1210,

    As I test it with excel and PowerBI desktop, everything working as expected. See below screenshot:

    But for other 3rd party tools, we can not guarantee that.


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by Arnav1210 Wednesday, April 11, 2018 5:17 PM
    Wednesday, April 11, 2018 7:25 AM
    Moderator

All replies

  • What client tool are you using? The way that formatting gets applied depends on the client tool and the query language that is used.

    http://darren.gosbell.com - please mark correct answers

    Monday, April 9, 2018 8:58 PM
    Moderator
  • Hi Arnav1210,

    Thanks for your question.

    If you want to show measure as percent,you might want to set format to "Percentage". If you are using SSAS tabular model with compatibility 1400, you can also set format to custom, format string to '0.00%;-0.00%;'

    For measures shown as whole number,  you might want to set format as whole number, set Decimal places to from 2 to blank as above image.

    The measure format string might also affected by the client tool as Darren mentioned, thus you may need to take care of that also.


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by Arnav1210 Wednesday, April 11, 2018 5:17 PM
    Tuesday, April 10, 2018 4:22 AM
    Moderator
  • I am checking an option wherein I can set Format String in SSAS model and same can reflect in any reporting tool like Power BI or excel or any 3rd party application.
    Tuesday, April 10, 2018 8:32 AM
  • I am checking an option wherein I can set Format String in SSAS model and same can reflect in any reporting tool like Power BI or excel or any 3rd party application.
    So PowerBI and Excel should work fine with the formatted values. But Microsoft can't control what other 3rd party tools do. Depending on how the data is queried client tools can either get a pre formatted value or they can get the value and the format string. So the information is available if the client tool chooses to use it.

    http://darren.gosbell.com - please mark correct answers

    Tuesday, April 10, 2018 10:23 PM
    Moderator
  • Thanks Darren !!

    But even after selecting Percentage as format string for measure in my tabular model, it's not showing up percent and for another measure, I have selected Whole number but still it's showing fractional values like233.78 but I want to round that to 234

    Wednesday, April 11, 2018 6:56 AM
  • Hi Arnav1210,

    As I test it with excel and PowerBI desktop, everything working as expected. See below screenshot:

    But for other 3rd party tools, we can not guarantee that.


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by Arnav1210 Wednesday, April 11, 2018 5:17 PM
    Wednesday, April 11, 2018 7:25 AM
    Moderator
  • Thanks Darren !!

    But even after selecting Percentage as format string for measure in my tabular model, it's not showing up percent and for another measure, I have selected Whole number but still it's showing fractional values like233.78 but I want to round that to 234

    Once again - what client tool are you using?

    http://darren.gosbell.com - please mark correct answers

    Wednesday, April 11, 2018 10:00 AM
    Moderator