Model assistance for Budget vs Actual

[sreed10]

I have read through many examples of budgets vs actuals, but still cannot wrap my head around what I need to do. (PBI user for 45 days)

I have a Sales Shipments Fact table that is based on invoice date. I have a fact budget table that has Customer, Month, and budget. Screen shots will hopefully clarify the model and the issue:  

[parry2k]

@sreed10 glad it worked, it is very hard to imagine and provide a solution without knowing all the details. I hope you understand. Anyhow, I guess, it is all good now 

[parry2k · Accepted solution]

@sreed10 still not clear what is not working? if you are using month from calendar dimension, it will filter both budget and sales tables for the same period? I think you are overcomplicating or I'm not able to understand your question. 

[sreed10]

I'm glad I mentioned I was a new user! The month slicer on the budget matrix was still using the Month from my Sales Shipment table. I added the Date dim later and forgot to update the slicer to use the Month value on the Date table. Thank you!

[sreed10]

Thank you for the replies and questions! I do have measures in place for the budget total and the revenue total, and I have a matrix showing YTD and % of annualized budget vs YTD revenue. But specifically what I'm after is how to model the tables so that I can do the same on a monthly basis. I have a slider representing the monthly revenue total that I would like to also then compare to the budget total for the selected number of months. Also, we do not budget by product so I'm still relying on that product detail to remina in the Sales Shipmetns fact table instead of a dimension. Our budget review is based on monthly performance and YTD. 

[parry2k]

@sreed10 model looks good, what is not working for you? What is your question? One thing I noticed is that the budget is at the monthly level and sales are ofcourse at the date level. So you have two options from here to compare sales vs. budget:

- compare only month and above granularity (like quarters/years)

- if you want to compare at the date level, it means the budget needs to be distributed to the date level, it can be an equal distribution, for example, if the budget is $3000 for April, means $300 per day ($3000/30 days). It is easy to spread at the date level, and after that, you can easily compare sales vs. budget at any granularity.

So what is the ask here?

[AndreDeLange]

Seems you're only interested in the budgeted $ vs actual $ and don't intend to report on budgeted vs actual products? Because if you were, then you would need a common product table.

The question isn't clear to me from the screenshots, but the way I would approach this is to have a measure for the Budgeted Amount = sum(Budget[Budget])
and a measure for the Sales Amount = sumx(ShipmentDetails,ShipmentDetails[ExtendedPR]*ShipmentDetails[Qty])

The reason I'm using SUMX is to calculate the $ Amount for each sales line in the ShipmentDetails table, given you don't have an existing column where this has already been done. If you did, the Sales Amount measure would simply have been definable as Sales Amount = sum(ShipmentDetails[SalesAmount]).

Next, you need a measure comparing the two:

Sales vs. Budget = [Sales Amount] - [Budgeted Amount]

You can further refine this measure to avoid showing zeros where there's no difference as follows:

Sales vs. Budget = var diff = [Sales Amount] - [Budgeted Amount]

                              return if(diff=0,blank(),dif)

When you put the Sales vs. Budget measure on a matrix visual, be sure to use a column from the Date/calendar table to visualise. I would guess you're interested in monthly buckets rather than daily, otherwise yes as parry2k suggests, you may need to make your budget data more granular.

Hope this helps, otherwise let me know if you have a more specific issue.