Calculate Field - Division - Mismatched Aggregation

Hi,

I am trying to create a calculated field which is essentially {Total Fee Value}/{Total Payment Received Onwards}. It is just to get an Average of the Total Fee Value per day as my visual is just a table which is grouped by a date and has many custom calculated field values.
The {Total Fee Value} field is a sum of 4 other calculated fields which all look like this with different states:
ifelse({CaseID Row Rank} = 1 AND (state = ‘State 1’ OR state = ‘State 2’), {totalfee}, 0)
The CaseID Row Rank is another calculated field which will just rank my data records as there are {CaseID} level duplicated within the data which all have {totalfee} and when it sums it I only want it to sum once per {CaseID} and that was the only way I knew how to do it.
The {Total Payment Received Onwards} is a sum of 4 other calculated fields which all look like this with different states: : distinct_countIf(caseid, state = ‘State 1’ OR state = ‘State 2’)
So I essentially have 4 pairs of matching calculated field where one will distinct count the {CaseIDs} and the other will sum the {totalfee}.

I am getting an error of Mismatched aggregation: ‘Custom aggregations can’t contain both aggregate “COUNT” and non-aggregated fields “COUNT(DISTINCT CASE WHEN…’

This is an example of how my data looks and why I created the {CaseID Row Rank}:

I am not sure about how to get this average field into my data/visual. I can provide further screenshots if that would be of any use.

Any help will be greatly appreciated, thank you.

Riley

What calculation is this failing on? the Total Payment Received Onwards?

Or when you divide the two?

Hi Max,

Thank you for your reply.

It is failing on the division part {Total Fee Value}/{Total Payment Received Onwards} with the error ‘Mismatched aggregation. Custom aggregations can’t contain both aggregate “COUNT” and non-aggregated fields “COUNT(DISTINCT CASE WHEN…’

Let me know if you need more information.

Thank you.

Riley

Total Fee Value is not aggregated while Total Payment Received Onwards is an aggregation.

You will need to aggregate Total Fee Value.

you can use sum({Total Fee Value})/{Total Payment Received Onwards}

Hi Max,

Ah, something so simple.

Thank you very much.

Riley