Measure with Row Level Calculations

[byr10112]

Hello,

I am trying to create a measure that will show me the full year forecast. There are 3 variables that go into the calculation. The measure:

Forecast = SUM(Revenue[Actuals]) + Sum(Revenue[Scheduled Revenue]) + SUM(Pipeline[Pipeline])

The tables are structured as follows:

Revenue:

Account ID	Date	Actuals	Scheduled Revenue
1001	01/20/2022	100
1002	04/20/2022	200
1003	01/02/2022	300
1004	11/06/2022	400
1005	12/30/2022		500

Pipeline:

Account ID	Date	Pipeline
1001	01/20/2023	100
1002	04/20/2023	200
1003	01/02/2023	300
1004	05/06/2023	400
1005	06/30/2023	500

I have Account ID and Calendar tables both tables connect to with the respective columns

There are some instances where we convert the pipeline into revenue, but the revenue hasn't been recognized yet. For example, lets say account 1006 had pipeline due December 6th and we converted it into revenue, but the revenue won't show up until December 18th. The pipeline table only shows the open pipelines (not converted/lost). As such the between December 7th and 18th, the forecast for account 1006 is incorrectly displayed as 0. To overcome this, I created a new table called Won Pipeline:

Account ID	Won Date	Won Pipeline
1001	11/20/2022	100
1002	12/01/2022	200
1003	01/02/2022	300
1004	11/06/2022	400
1006	12/06/2022	1000

I want to update my Forecast measure to display the Won pipeline if the calculation for the forecast is 0 or blank. I changed my formula to this:

Forecast =
var _a= SUM(Revenue[Actuals]) + Sum(Revenue[Scheduled Revenue]) + SUM(Pipeline[Pipeline])

var _b= SUM(Won Pipeline[Won Pipeline])

RETURN

IF( OR(ISBLANK(_a), _a=0), _b,_a)

The problem is, when I want to see the all up forecast, Won Pipeline is not being included in DBF, as in an all up view, _a is not blank or not equal to 0. Is there a way to update my measure so the all up numbers include the won pipeline for the accounts where there is no revenue, scheduled revenue or open pipeline, but there is a Won Pipeline?

Thanks

[jgeddes]

Can you share what you wrote and the value(s) that are being returned?

[byr10112]

I copy and pasted your formula and changed the table and field names accordingly. Account 1006 is still showing 0, for some other accounts pipeline and/or actuals are appearing as 0

[jgeddes]

Ok. Let's try and troubleshoot this. 

If I create a new calculated table with...

Forecast Table = 
var _vtable =
SUMMARIZE(
    accountTable,
    accountTable[Account ID],
    "_Forecast", IF(OR(ISBLANK(SELECTEDVALUE(revenueTable[Actuals]) + SELECTEDVALUE(pipelineTable[Pipeline]) + SELECTEDVALUE(revenueTable[Scheduled Revenue])), SELECTEDVALUE(revenueTable[Actuals]) + SELECTEDVALUE(pipelineTable[Pipeline]) + SELECTEDVALUE(revenueTable[Scheduled Revenue])=0), SELECTEDVALUE(wonPipelineTable[Won Pipeline]), SELECTEDVALUE(revenueTable[Actuals]) + SELECTEDVALUE(pipelineTable[Pipeline]) + SELECTEDVALUE(revenueTable[Scheduled Revenue]))
)
Return
_vtable

I get...

If you create a new table with that formula (changing the table/column names to match yours) what table do you get?

[byr10112]

I tried doing this already. The same thing happened, for 1006 it appeared as 0

[jgeddes]

You can try the following measure (it is ugly, but it seems to work).

Forecast = 
var _vtable =
SUMMARIZE(
    accountTable,
    accountTable[Account ID],
    "_Forecast", IF(OR(ISBLANK(SELECTEDVALUE(revenueTable[Actuals]) + SELECTEDVALUE(pipelineTable[Pipeline]) + SELECTEDVALUE(revenueTable[Scheduled Revenue])), SELECTEDVALUE(revenueTable[Actuals]) + SELECTEDVALUE(pipelineTable[Pipeline]) + SELECTEDVALUE(revenueTable[Scheduled Revenue])=0), SELECTEDVALUE(wonPipelineTable[Won Pipeline]), SELECTEDVALUE(revenueTable[Actuals]) + SELECTEDVALUE(pipelineTable[Pipeline]) + SELECTEDVALUE(revenueTable[Scheduled Revenue]))
)
Return
SUMX(_vtable, [_Forecast])

I ended up with the result

[byr10112]

Its not giving me correct results. Not sure what is broken, but this is giving me a significantly lower number.