Expression for Avg of percentages

Question

Expression for Avg of percentages

polkadot

SSChampion

Points: 13664
More actions
August 11, 2014 at 8:29 am

#397180

In SSRS report is column "conversion rate" and on each row a percentage.
At bottom of report I need the average of averages and need to handle for rows where no data was available so average was 0.00.
Please tell me what is the SSRS expression for this?
--Quote me

Viewing 6 posts - 1 through 5 (of 5 total)

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Try the formulas posted on this link.

http://stackoverflow.com/questions/23230172/need-ssrs-expression-for-percent-of-change-that-avoids-division-by-zero-error

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

polkadot SSChampion Points: 13664 More actions · Answer 2

SQLRunner,

Are you suggesting that my main problem is 'division by zero'?

At the bottom of my column of percentages I need the average of percentages.

I've tried

=AVG((Fields!To_Your_Store.Value+Fields!Lost_to_Competitors.Value)/Fields!TotalLeads.Value))

and result is 'Infinity'. In another column where I tried this the result is 'NaN'

Are you saying that problem will be resolved when I handle for the zeros?

--Quote me

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

polkadot (8/11/2014)
SQLRunner,
Are you suggesting that my main problem is 'division by zero'?
At the bottom of my column of percentages I need the average of percentages.
I've tried
=AVG((Fields!To_Your_Store.Value+Fields!Lost_to_Competitors.Value)/Fields!TotalLeads.Value))
and result is 'Infinity'. In another column where I tried this the result is 'NaN'
Are you saying that problem will be resolved when I handle for the zeros?

Should when you handle for NULLS and handle for Zeros.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

KoldCoffee SSCoach Points: 18971 More actions · Answer 4

Can you show me how to change the expression that I use for a column called Total Conversions? A zero can potentially appear in either of the supplied numerator values:

originally:

=(Fields!To_Your_Store.Value + Fields!Lost_to_Competitors.Value)/Fields!Leads.Value

I tried:

=IIF((Fields!To_Your_Store.Value + Fields!Lost_to_Competitors.Value)>0,(Fields!To_Your_Store.Value + Fields!Lost_to_Competitors.Value)/Fields!Leads.Value,0)

no error, just no change to behavior of AVG(Total Conversions) which I need at the very bottom of report on the TOTALs line. I need an average of all percentages and am still getting 'Infinity' and 'NaN'.

Can someone offer solution for getting the Average percent of all percentages using SSRS expressions?

KoldCoffee SSCoach Points: 18971 More actions · Answer 5

I needed this:

=IIF((Fields!To_Your_Store.Value + Fields!Lost_to_Competitors.Value)>0 AND Fields!Leads_Sent_to_Your_Store.Value > 0,(Fields!To_Your_Store.Value + Fields!Lost_to_Competitors.Value)/Fields!Leads_Sent_to_Your_Store.Value,0)

it basically says, if the numerator is greator than zero AND the demoninator is greator than zero, then return zero, otherwise divide the numerator by the denominator.

Now that's handled, the AVG(Total Conversions) at bottom of report column, works.

SQL Runner, thanks for letting me know I was on right track and giving me the needed nudge. Appreciated the link too.

this link was good

http://social.msdn.microsoft.com/Forums/en-US/cb0d016b-83be-4720-8ffc-c4af48f4a5a0/ssrs-iif-syntax