Creating Plan / Actual for Year & Months

DAX DAX Calculations
1

Forecast%20and%20Actual%20combined
Forecast and Actual combined.PNG801×203 17.2 KB

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?

2

@Rosc10

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

Nick

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

3

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

4

Power%20BI%20Forecast%20Actual%20Help
Power BI Forecast Actual Help.JPG952×728 168 KB

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.

5

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

6

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

7

Sum EXFACTORY FOR CUSTOMERMONTH Problem.pbix (36.1 KB)

8

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.

image
image1548×378 44.9 KB

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]) )

image
image1493×346 42 KB

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.

http://portal.enterprisedna.co/courses/108877/lectures/2000574

http://portal.enterprisedna.co/courses/108877/lectures/2399489

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