Dashboard Designer percentage calculation

  • hex226

    Valued Member

    Points: 70

    Hi,

    I wonder if anyone can help me.

    I have created a couple of grids which show percentage columns.

    However, the data is populated throughout the year and as there is no data as yet the calculation is effectively 0/0.

    But rather than displaying 0%, the grid is displaying -1.#IND.

    Is there anyway to get the column to display 0%, when there is no data as yet?

  • denglishbi

    SSCrazy Eights

    Points: 8831

    You would want to handle this in your calculation. You are running into a division by zero here. Might need to see your exact calculation, but when you are doing division you need to check for this with say an IIF statement like the following:

    IIF(b=0,NULL,a/b)

    Depending on the version of SSAS you are using and not sure if you are doing multidimensional or tabular, but you can also use DIVIDE function as well now.

    You can then decide if you want to display empty values in your analytical report or not.

    If you want to return a zero in the measure you wouldn't want to do it in the calc because of performance, but you could do it in the FORMAT_STRING if you are doing multidimensional. You can specify how to handle zero and null values with the FORMAT_STRING settings.

    ----------------------------------------------------------------------------------------
    Dan English - http://denglishbi.wordpress.com

  • hex226

    Valued Member

    Points: 70

    Thanks Dan,

    I have decided to use the DIVIDE function, and leave the value blank.

    I've just started developing a dashboard using the Tabular Model.

    I'm reading a book at the moment, but noticed you have a blog. I'll certainly be reading that for some guidance 🙂

  • denglishbi

    SSCrazy Eights

    Points: 8831

    Sounds good, glad you were able to resolve the issue and are making progress!

    Good luck:)

    ----------------------------------------------------------------------------------------
    Dan English - http://denglishbi.wordpress.com

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply