Hi All,
any help on the below.
Calculation one = Unit price Current Year
I have a measure on a report say Sales and also a measure which has quantity and then a data item calculating unit price = Sales / Quantity.
Calculation two = Unit price prior Year
= prior year sales / prior year quantity (both measures nested with the current month)
Calculation three = Variance in the Unit Price.
If i have quantity for both calculations then the report works fine, however if i have no quantity for a period then i get in the variance column /0 sign .
Does any body know how to calculate the variance correctly even if there is a zero quantity for one of the calculations. The format option does not work.
= Difference in the unit price ie Calc one less Calc two
Variance should be: ([Current Year]-[Prior Year])/[Prior Year)*1
Select the Variance field in report, in properties -- Data Format-- Set it to 'Percent' --- You can see an option 'Divided by Zero value' character, set it to '-' or how u wish to display.
Hope this resolves?
Thanks
Prit
It did not work.......
Say in one calc i have the 10 and the second i have 5 then the variance should be 5.
However if there is no quantity in the first calc it is givinmg me a /0 and because i am using this in my calcuation in another item it displays /0
Any help
How about changing the underlying calculations to use a conditional statement:
if ( [Quantity] = 0 )
then ( 0 )
else ( [Sales] / [Quantity]
thanks it worked, sometimes second set of eyes is better.
Help is much appreciated.
Just started my journey into BI
Glad it worked out :)
It worked for a simple query but not for a complex query i have , does anyone know if i have a calcuation like this how to resolve it...
if i have Sales/ Quantity but quantity can be zero can you have some type of syntax which ignores the error and puts in a value in the calculation.
Sorry if this is not clear
Can you describe what is complex about the query and what doesn't work? Do you get an error or just incorrect results? What is the expression?