Budget vs. Actuals

[gbogue]

Hi PowerBI beginner here

I have actual, budget and forcast data for revenue, volume and gross profit in the format.

I want to show the Actual versus Budget with a variance column in a matrix and a chart. 

I have saved a sample of the data below

https://drive.google.com/file/d/1k68rHWQ3NAzbktIqEO-Rp3KZDFYtmivv/view?usp=sharing

Thanks in advance

Garry

[Anonymous · Accepted solution]

@gbogue  - You can specify Measures for the various combinations of attributes. For example:

Actual Revenue = 
CALCULATE( 
    SUM(Data[Revenue]),
    Data[FinanceActivity] = "Actual"
)

Then create other measures for the variance information.

Cheers!

Nathan

[Anonymous]

Hi @Anonymous 

I tried doing this however when i created actual it was fine however when i went to create one for planned it was saying that there was a circular dependency detected

Would you know how to solve this?

Thank!

[Anonymous]

@Anonymous Were you able to resolve this problem. I am working on a similar problem and not sure how to align the data source and create a matrix of Actuals, Budgets and %Change. It would be really nice if you could share a sample file where you got it worked out.

Thanks in advance.

[Anonymous]

@Anonymous - Could you share your pbix?

Ideally, you would have 2 fact tables (budget and actuals) with a date dimension table that is related to each fact table (1 date - many facts).

Then, you can create 3 Measures to sum the actuals, sum the budget, and calculate %.

Finally, put all 3 measures in your matrix, along with whatever timeframe is of interest.

I hope this helps. If it does, please Mark as a solution.
I also appreciate Kudos.

[Anonymous]

@Anonymous  Thanks for the quick response.

So, I have 4 'Marketing channels' and each channel has 'Actual', 'Budget' and 'Plan' values for 3 different measures (Applications, Approval Rate, and Funded Rate' for every month. 

The business is going to provide me with budget and plan values by channel, for each month. I am confused how to set up the data model.

Now, I am supposed to create a matrix tha would some thing like:

KPI	Actual	Plan	%Change
Applications	100	120	20%
Approval Rate	30%	35%	5%
Funded Rate	40%	35%	-5%

Could you please suggest what approach should I take here.

[Anonymous]

@Anonymous 

First, it is going to be problematic to create such a matrix. Specifically, Power BI requires a single format per numeric measure, so you won't be able to have whole numbers and % in the same column unless you convert it to text (not recommended). There is an idea to enhance this. If you do want to use text formatting, or lose the formatting, you could do the following:

Tables:

1. Date dimension table

2. Channel dimension table

3. Budget/Plan fact table, with measures Budget Applications, Budget Approval Rate, Budget Funded Rate, Plan Applications, Plan Approval Rate, Plan Funded Rate

4. Actuals fact table with measures Actual Applications, Actual Approval Rate, Actual Funded Rate

5. Disconnected Parameters table with KPI column, that contains values of your different KPIs. It could also have a Budget vs Plan column, so you can switch between Budget and Plan. If you have multiple columns in your Parameter table, they need to be a cross-product, so this would be 6 rows.

This table will also have measures for combining the various KPIs, e.g. 

Actual = 
var _sel = SELECTEDVALUE(Parameters[KPI])
return SWITCH(
    _sel,
    "Applications", [Actual Applications],
    "Approval Rate", [Actual Approval Rate],
    [Actual Funded Rate]
)

Relationships:

1. 1-M relationship between Date, Actuals (assuming actuals is at day grain)

2. M-M relationship between Date, Budget/Plan

3. 1-M relationshipo between Channel and each of Actuals and Budget/Plan

4. NO relationship to the Parameters table.

I hope this helps. If it does, please Mark as a solution.
I also appreciate Kudos.

[Anonymous]

@Anonymous Thanks for sharing all the information.

Could you please share with me how should the three tables (Budget,Actual and the Disconnected Params table) look like at the back end ( column names and 2-3 rows values). What attributes should there be present in the tables to create my measures?

I have to ask the business to provide me the 'budget' and 'plan' values for the 4 channels for each month in an excel file and I am unable to decide how the excel template should look like so that they can enter the values and it would be easy for PowerBi to digest as well when I work on the data model.

I have posted my entire project is this link : https://community.powerbi.com/t5/Desktop/Actuals-vs-Budgets-Scorecard/m-p/801139#M385495

I hope that will give a better picture of what I am looking for.

[Anonymous]

* budget not planned

[Anonymous]

@Anonymous - Could you share your pbix - I'm not sure what your data model looks like, or what measures you created.

Cheers,

Nathan