SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Dashboard Designer percentage calculation


Dashboard Designer percentage calculation

Author
Message
hex226
hex226
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 2
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
denglishbi
SSCertifiable
SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)

Group: General Forum Members
Points: 6633 Visits: 1364
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
hex226
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 2
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
denglishbi
SSCertifiable
SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)

Group: General Forum Members
Points: 6633 Visits: 1364
Sounds good, glad you were able to resolve the issue and are making progress!

Good luckSmile

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


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search