Make a customized measure equal to another measure in particular cases

Questions & Answers
, , ,
1

Hello!
I’m making a report. What I need to see is estimated hours, spent hours and difference between them. I customized a difference measuse, it works fine, of course if the spent hours number is bigger then the estimated hours numbers I receive a negative number, but it’s ok.
What I need: when estimated hours is empty or it is 0, I want to see in “Difference” just a number of hours spent without “-”. I’d appreciate if anyone suggests a formula for that.
Thank you) Have a great day!

image
image964×684 27.6 KB

2

Try ABS([Measures].[Original estimated hours…]-[Measures].[Hours spent]),

Without formula you can change your subtraction: [Measures].[Hours spent]-[Measures].[Original estimated hours…], but this only works if [Hours spent] is always greater than [Original estimated hours]

3

Hi @Natalia ,
I see that @_P3 already have suggested solution with ABS().
You can use it in CASE WHEN statement to return this result when measure “Original estimate hours” is empty, else it will return your original formula:

CASE WHEN 
  IsEmpty([Measures].[Original estimated hours])
THEN
  ABS([Measures].[Original estimated hours]-[Measures].[Hours spent])
ELSE
  [Measures].[Original estimated hours]-[Measures].[Hours spent]
END

Or instead of ABS(), you can use CoalesceEmpty() to return measure “Hours spent” if measure “Original estimate hours” is empty.

CASE WHEN 
  IsEmpty([Measures].[Original estimated hours])
THEN
  CoalesceEmpty([Measures].[Original estimated hours], [Measures].[Hours spent])
ELSE
  [Measures].[Original estimated hours]-[Measures].[Hours spent]
END

Best,
Gerda // support@eazybi.com