Measure Total Problem

[GE1]

Hi

I am struggling with a measure total. I've been through the solutions online but they don't seem to work (obviously, my application of these solutions is not correct). I have a lead forecast by month, as below. When actual leads come in (see the second column) this adjusts the forecast to a 'new estimate'. That all works fine, except the total of the new estimate is wrong, since it is only adding up part of the calculated result.

The leads are first added together from two tables:

Actual Leads = SUM( 'Lead Source 1'[Leads])+SUM('Lead Source 2'[Leads])

Everything else is in a table called 'LEAD FORECAST':
ShortMonthYear
Leads Forecast

The new forecast is a simple IF:

New Estimate = if ('LEAD FORECAST'[Actual Leads] = 0 , sum('LEAD FORECAST'[Leads Forecast]), 'LEAD FORECAST'[Actual Leads])

The total of this column is wrong. It only adds up 'Actual Leads' since at the total level, leads do not equal zero. This is my attempted fix:

Total Fix 1 =

VAR __table = SUMMARIZE('LEAD FORECAST',[ShortMonthYear],"__value",[New Estimate])

RETURN

IF(HASONEVALUE('LEAD FORECAST'[ShortMonthYear]),[New Estimate],SUMX(__table,[__value]))

This corrects the error in the original formula by adding in the estimates to the total - but it then excludes the Actual Leads. So it produces the exact opposite result and is still wrong.

If I try to add the 'Actual Leads' to the total, it adds just 158 instead of 14,560:

Total Fix 1 =

VAR __table = SUMMARIZE('LEAD FORECAST',[ShortMonthYear],"__value",[New Estimate] + [Actual Leads])

RETURN

IF(HASONEVALUE('LEAD FORECAST'[ShortMonthYear]),[New Estimate],SUMX(__table,[__value]))

I don't know why. But this next 'fix' re-creates the 158 on the individual lines, without adding it to the total:

Total Fix 2 =

VAR __table = SUMMARIZE('LEAD FORECAST',[ShortMonthYear],"__value",[New Estimate])

RETURN

IF(HASONEFILTER('LEAD FORECAST'[ShortMonthYear]),[New Estimate],SUMX(__table,[__value]))

So - what do I need to do to get a total of 98,264?

Thank you

[GE1]

Thank you. That didn't work but the concept did - it seems I was trying to add up the two columns too early, so it wasn't getting the right value row-by-row.

Actual Leads = SUM( 'Lead Source 1'[Leads])+SUM('Lead Source 2'[Leads])

Estimate Remaining = if ('LEAD FORECAST'[Actual Leads] = 0 , sum('LEAD FORECAST'[Leads Forecast]), 0)

Leads to add to actual total = VAR __table = SUMMARIZE('LEAD FORECAST',[ShortMonthYear],"__value",[Estimate Remaining])

RETURN

IF(HASONEVALUE('LEAD FORECAST'[ShortMonthYear]),[Estimate Remaining],SUMX(__table,[__value]))

New Estimate = 'LEAD FORECAST'[Leads to add to actual total] + 'LEAD FORECAST'[Actual Leads]

[Wilson_]

Hey GE1,

Try this:

SUMX (
    'LEAD FORECAST',
    COALESCE ( 'LEAD FORECAST'[Leads Forecast], 'LEAD FORECAST'[Actual Leads] )
)

The key is to go row by row and get the value, then sum all the values of the rows together at the total level. Let me know if that's not what you were looking for. (From looking at your post, it looks like ShortMonthYear, Actuals Leads and Leads Forecast are all columns in the same table.)

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)