Tracking budget vs actual

[tstackhouse]

I am attempting to track 4 measures for each of our sales people.  Net Sales vs Budgeted YTD and Forecasted Net Sales vs Budgeted. Below is a break down of what each entails:

Net Sales- Net Sales YTD,  column from my database

Budgeted YTD- Where salesperson should be based on the budget and the day of the year

Forecasted Net Sales- Projected Net Sales to the end of the year based off what our current sales are and prorated for the remainder of the year

Budgeted- Where the salesperson should be at the end of the year based on the budget

I was able to do something similar in tableau picture below.

Since I am not able to attach a packaged workbook, I will try to explain my data model.

I have two tables:

1. Transaction Table-Sales data YTD by customer and Sales person and date, simple transaction table aggregated to customer date level

2.Budget Table-budgeted sales by customer by day

I joined the two tables on a concatenation of customer and date.

So far I have run into two issues.

1. I cannot figure out how to get Net Sales prorated to the end of the year (as seen below as the dark blue bar), notice it is much lower than the related orange bar

2. I cannot figure out how to get all 4 measures on to one graph. Right now I have two column charts for each sales person. I have the YTD (left one) filtered on my date from the transaction table. Given that this table only goes up to the current date, it works as a filter to limit the budgeted table up to the current date. Ideally it would be nice to have them combined (as shown in the tableau bar chart)

Tableau:

Power BI:

[v-piga-msft]

Hi @tstackhouse ,

I'm afraid that the visual in Tableau should be a little different from that in power bi.

Have you solved the problem with the suggestion of Mariusz ?

If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please share some data sample and your desired output so that we could help further on it.

Best  Regards,

Cherry

[tstackhouse]

Is there a way that I can attach a data set? I feel that it would help as well.

[v-piga-msft]

Hi @tstackhouse ,

Have you solved your problem?

If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please feel free to ask.

Best  Regards,

Cherry

[Mariusz]

Hi @tstackhouse 

You can share your pbix file through MS One Drive, dropbox or google drive and paste the link in your post or just paste the data from excel.

Best Regards,
Mariusz

Please feel free to connect with me.

[Mariusz]

Hi @tstackhouse 

Please see the articles below, this will give you some ideas how to handle budgets with DAX.
https://www.daxpatterns.com/budget-patterns/
https://www.sqlbi.com/articles/showing-actuals-and-forecasts-in-the-same-chart-with-power-bi/

https://www.sqlbi.com/articles/budget-and-other-data-at-different-granularities-in-powerpivot/

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.