Hi,
We are facing an issue with the domo beast mode calculation logic. Below is the problem along with the logic -
If the values of column A,B,C,D,E, Quantities, part no 1, part no 2 are same and the count of value for these columns (A-part no 1, A_part no 2, M_part no 1, M_part no 2, E_part no 1, E part no2) is greater than 1 then we need to take the average of that quantity. For example
When we are using below mentioned logic then we are getting average of quantities on a line chart i.e. 9000 (y axis - Quantities, x axis -D) for the above image values.
But when we are adding one more record having different part no 1 or part no 2 to the above records then we are getting the summation of all the quantities rather than taking the average of above and summation of the quantity of new record.
Expected sum of quantities on a line chart 14000 + avg(9000 + 9000) = 23000
but we are getting the sum of all the quantities 14000 + 9000 + 9000 = 32000
We have used the below logic
CASE
WHEN COUNT(DISTINCT CONCAT(A
, B
, C
, D
, E
,Quantities
,part no 1
, part no 2
))=1 AND
COUNT(DISTINCT CONCAT(A_part no 1
, A_part no 2
, M_part no 1
, M_part no 2
, E_part no 1, E_part no 2))>1
THEN AVG(Quantities
)
ELSE SUM(Quantities
)
END
If we are adding more records of different part no. than it is taking sum of all the quantities rather than average for the duplicated quantities along with summation of remaining.