Creating Plan / Actual for Year & Months

I have used DAX to create a combined Plan / Actual forecast for the rest of the year. Same methodology also working for Forecast / Actual row in the attached table.

e.g. Plan/Actual (GSV) = IF([Ex Factory (GSV)]=0,[Plan (GSV)],[Ex Factory (GSV)])

However, the Total for the year is incorrect as the formula seems to consider that Ex Factory has a value for the year, and so takes that value rather than calculate each month individually and then summing all the Monthly results. How can I fix the Annual Total?

@Rosc10

Any chance you can load some sample data? Hard to see exactly in the screenshot

Nick

Enterprise%20DNA%20Expert%20-%20Small

As per Nick above, bit difficult to assist here with just what’s provided.

Will likely need to see the underlying formulas and also a demo model to see everything else at play in this scenario.

Thanks
sam

Hi I’ve dummied up how I would do it in Excel in the attached Screenshot.

Essentially I want to test whether TOTALCustomerMonth has sales (and assign this result to the SKUMonth Row)
Then use that result to choose whether ACTUALSALES or FORECAST is populated into the Result Cell). Note: A standard ‘If Cell = 0, then take the Forecast’ will be incorrect if the cell just happens to have zero sales for the month.

Appreciate the data here but I’m personally struggling to picture how this would all work in Power BI.

Can you add the actual Power BI file or a demo so we can look at the whole picture including model, relationships, DAX formulas etc.

Thanks

I’m quite confident that this just requires an iterating function like SUMX instead of the IF statement, as you need to be iterating through every row here.

Totals usually don’t add up correctly when using IF statements, that’s why I believe this is where is not working.

Here’s a good tutorial to review around iterating functions

And another good article to go over

Sum EXFACTORY FOR CUSTOMERMONTH Problem.pbix (36.1 KB)

Ok first thing here.

Don’t use Calculated Columns. They are unnecessary. You want to be using measures wherever possible.

This is where this has come unstuck straight away and why I was really unable to give a solid answer straight away, because I wasn’t aware that these calcs where from columns instead of measures.

One thing I always mention around DAX is always just start with the simplest measures. Like I have done here.

From here this is where you need to start using iterating functions, like mentioned above, so that you can work through each row and run some logic.

For example

Forecast/Actual = 
SUMX( DNA_HELP,
    if(DNA_HELP[EXFACTORY ACTUALS]=0,DNA_HELP[FORECAST],DNA_HELP[EXFACTORY ACTUALS]) )

Required Forecast = 
SUMX( DNA_HELP,
    if(DNA_HELP[Sum EXFACTORY FOR CUSTOMERMONTH]=0,DNA_HELP[FORECAST],DNA_HELP[Sum EXFACTORY FOR CUSTOMERMONTH]) )

Hopefully you are getting what I’m doing here. This is really important stuff and concepts you need to master as early as you can.

Here’s so videos to work through.

This part here I’m a bit confused about and don’t believe you really need it? This isn’t something I would recommend

image

I’m confident you could work out what you require in a measure quite easily.

Give this a go and see where you get to.

Model attached below
Sum EXFACTORY FOR CUSTOMERMONTH Problem.pbix (46.1 KB)

Sam