CASE [Label].CurrentMember.Level.Name
WHEN ‘PI-6’ THEN ‘N/A’
WHEN ‘PI7’ THEN
([Measures].[Sprint issues committed] - PreviousRowValue([Measures].[Sprint issues committed]))/ PreviousRowValue([Measures].[Sprint issues committed])
END
Hello @ysharma
I think this is a “formatting” issue. When PI-6 your formula returns a string (‘N/A’) and when PI7 it returns a number (that you format as percentage). Value “infinity” may be the result of dividing N by A…
What is happening if you change the ‘N/A’ to a numeric value?
Or try the other way around… Change the formatting of your “improvement” to “default”. If the result (number 2) is displayed correctly we will add the “%” later!
Hope it helps!
VasileS.
Thank you so much @VasileS
I changed it to default and not sure what I changed but now even that ‘2’ is not coming now…
CASE [Label].CurrentMember.Level.Name
WHEN ‘PI-6’ THEN 0
WHEN ‘PI7’ THEN
([Measures].[Sprint issues committed] -
PreviousRowValue([Measures].[Sprint issues committed]))/
PreviousRowValue([Measures].[Sprint issues committed])
END
it’s because “[Label].CurrentMember.Level.Name”. Use CurrentHierarchyMember.Name
Try to go step by step and see what is shown.
I used “Issue type” instead of “Label” for my calculated measure - you’ll figure it. Format is ‘decimal’:
--[Issue Type].CurrentMember.Level.Name
--[Issue Type].CurrentHierarchyMember.Name
CASE [Issue Type].CurrentHierarchyMember.Name
WHEN "Epic" THEN 9999
WHEN "Task" THEN
IIf( -- to avoid 'division by zero'!
[Measures].[Issues resolved] > 0,
[Measures].[Open issues] / [Measures].[Issues resolved],
9999
)
END
/* ***
CASE [Label].CurrentMember.Level.Name
WHEN ‘PI-6’ THEN 0
WHEN ‘PI7’ THEN
([Measures].[Sprint issues committed] -
PreviousRowValue([Measures].[Sprint issues committed]))/
PreviousRowValue([Measures].[Sprint issues committed])
END
*** */
Then create a new measure to display the percentage (format: default):
IIf(
[Measures].[zzz] > 100,
'N/A',
Format([Measures].[zzz],'###.##%')
)
My report looks like this:
You may want to use FORMAT in your formula, so there’s no need to have multiple custom measures. I keep them so the calculation steps can be clearly visible.
References
EazyBI docs: Format
MDX reference: FORMAT_STRING Contents (MDX) | Microsoft Learn
Kind regards,
VasileS.
Now actually I have tried this below
CASE
WHEN [Label].CurrentMember.Name MATCHES ‘PI-6’
THEN
‘N/A’
WHEN [Label].CurrentMember.Name MATCHES ‘PI7’
THEN
([Measures].[Sprint issues committed])
END
and I am getting this
If I Do like this
CASE
WHEN [Label].CurrentMember.Name MATCHES ‘PI-6’
THEN
‘N/A’
WHEN [Label].CurrentMember.Name MATCHES ‘PI7’
THEN
‘OK’
END
Then this is the result
You are on the right track!
Change the ‘OK’ in your code here:
WHEN [Label].CurrentMember.Name MATCHES ‘PI7’
THEN
‘OK’ -- <--- replace this with the code below
END
as follows:
Format(
([Measures].[Sprint issues committed] - PreviousRowValue([Measures].[Sprint issues committed]))
/
PreviousRowValue([Measures].[Sprint issues committed])
,
'###.##%')
Make sure PreviousRowValue([Measures].[Sprint issues committed] is not zero!
Kind regards,
VasileS.
I have done something like below
One customer measure : Improvement in Committed SP
[
CASE [Label].CurrentMember.Name
WHEN ‘PI5L’ THEN
([Measures].[Sprint Story Points committed] -PreviousRowValue([Measures].[Sprint Story Points committed]))
/PreviousRowValue([Measures].[Sprint Story Points committed])
WHEN ‘PI6L’ THEN
([Measures].[Sprint Story Points committed] -PreviousRowValue([Measures].[Sprint Story Points committed]))
/PreviousRowValue([Measures].[Sprint Story Points committed])
WHEN ‘PI7L’ THEN
([Measures].[Sprint Story Points committed] -PreviousRowValue([Measures].[Sprint Story Points committed]))
/PreviousRowValue([Measures].[Sprint Story Points committed])
END]
Formatting is default…
then second custom measure is: Improvement in Committed SP Percent
[
IIf(
[Measures].[Improvement in Committed SP] > 100,
‘N/A’,
Format([Measures].[Improvement in Committed SP] ,‘###.##%’)
)]
Then it shows like below table… which then I remove first custom measure “Improvement in Committed SP” and keep only second measure Improvement in Committed SP Percent