Multiply and sum not working together

Questions & Answers
, ,
1

I want to multiply 2 measures and then get the sum of them. One measure can be empty at times which makes the calculation wrong.

Please see the attachment, I’m multiplying values Col1Col2 into Col3.
But the sum of Col 3 is shown as Sum Col1 Sum Col2, insted of 200 which is the actual sum of values under Col 3.

Script used for test 1 (Col 3)

CASE WHEN [Measures].[Crew Hours On Site created] = 0 THEN 0
WHEN NOT IsEmpty([Measures].[Crew Hours On Site created])
THEN
** [Measures].[Crew Hours On Site created]***
** [Measures].[Crew Size created]**
END

eazybi1
eazybi1281×504 8.6 KB

2

CASE WHEN [Measures].[Crew Hours On Site created] = 0 THEN 0
WHEN NOT IsEmpty([Measures].[Crew Hours On Site created])
THEN
[Measures].[Crew Hours On Site created]*
[Measures].[Crew Size created]
END

3

Hi @gmjohn,

For each row, the calculated measure multiplies “Crew Hours On Site created” and “Crew Size created” values that are on that same row. Like in your example, 10.00 * 5.00 = 50.00.
By default, the same logic works for the Total row, it multiplies the two values, “Crew Hours On Site created” and “Crew Size created”, which are on the Total row. In other words, the order of mathematical operations is first sum up Total for each measure separately and then multiply those values.

If you would like to change the default logic and sum up already calculated values for each row, you might want to add the annotation at the beginning of the formula: -- annotations.total=sum.
More on annotation are here: https://docs.eazybi.com/eazybijira/analyze-and-visualize/calculated-measures-and-members#Calculatedmeasuresandmembers-Specialcommentswithannotations.

The updated formula would look like this:

-- annotations.total=sum
CASE WHEN [Measures].[Crew Hours On Site created] = 0 THEN 0
WHEN NOT IsEmpty([Measures].[Crew Hours On Site created])
THEN
  [Measures].[Crew Hours On Site created]*
  [Measures].[Crew Size created]
END

This option to change Total row behavior with annotation is available from eazyBI version 6.0.1 and on Cloud.

Best,
Zane / support@eazyBI.com