How to calculate 2 fields divided by a condition

So, I’m able to see the information needed on a Pivot Table, but I need to make a Calculated field in order to obtain the final value on a single row with “a Weighted average” but currently is separated by a “concession type”.
It goes like this:
X = “Goal % achieved of promo” on ww01 is 70.3%
Y = “Goal % archived on reversal” on ww01 is 137.8%
So Weighted average is to get is ( X + Y) / 2

on the screenshots, you can see the values for ww01 and ww02
and the result that I’m looking should be:
ww01 = (137.8% + 70.3%) / 2 = 104.05%
ww02 = (149.4% + 75.2%) / 2 = 112.3%

Im trying and ifelse formula but i’m not getting any luck.

sampling

So you only want to add weight it if it’s CLR% achieved and promo_gc and prs%achieved and reversal.

Let’s call the clr vs prs field {achieved_field}

have you tried this?

sum(ifelse({achieved_field} = ‘CLR% Achieved’ AND {concession_type}=‘promo_gc’,{value_field},NULL) + ifelse({achieved_field} = ‘PRS% Achieved’ AND {concession_type}=‘reversal’,{value_field},NULL))/avg(2)

1 Like

Hi @riaandr!
Did the calculation provided answer your question? If so, please help the community out by marking this answer as “Solution!”

Actually yes it did.
I had a Mismatch aggregation issue and I tried the formula provided on the answer, but it failed again until I went back to a previous formula to calculate de CLR% and PRS% with the syntax correction.

I realize is the same solution that is on Mismatched aggregation issue

So I change the formula order, instead of start with ifelse I started with sum (ifelse…) to:

sum(ifelse ({Concession Type Code} = “reversal”, {# correct reversal}, null))/count({total reversal})

that’s it, thanks to all.