Sum if is null divided by Count

[Shir63]

Hi everyone,

I want to write a code at the front where if is null "Revenue Full path"

then sum X * Y

else 

Sum(REVENUE_FULL_PATH)

/

count(distinct total <[Opportunity Number]> [Opportunity Item Product Code Adjusted]))

but it gives me a total different than the one i see when exporting to excel.

is my formula wrong?

if(isnull(REVENUE_FULL_PATH), sum([Opportunity Total Price] * "Opportunity Conversion Rate Today($)"),

Sum(REVENUE_FULL_PATH)
/
count(distinct total <[Opportunity Number]> [Opportunity Item Product Code Adjusted]))

thank you!

[MayilVahanan]

HI @Shir63 

If its straight table,  use below option change from Auto to Sum.

If its pivot table, then use aggr() like below

Sum(Aggr(if(isnull(REVENUE_FULL_PATH), sum([Opportunity Total Price] * "Opportunity Conversion Rate Today($)"),

Sum(REVENUE_FULL_PATH)
/
count(distinct total <[Opportunity Number]> [Opportunity Item Product Code Adjusted]))

, yourchartdimension))

Hope it helps