Actual vs Budget with two variables (monthly)

[ContabilidadBI]

Hi!

I would like to do an actual vs budget monthly comparison but including two variables, the quantity sold and the price, so I can identify which part of the deviation is due to a deviation in the sales price and which is due to a deviation in the quantity sold. This is a part of my fact (sales table):

CODLAL	CodProducto	Cantidad	Precio	Coste	CodFormato	CodColor	Fecha	CodCliente
180002	P014	8, €	6,24 €	4, €	T1	C4	05/01/2018	2
180002	P030	4, €	6,8 €	5, €	T1	C4	05/01/2018	2
180002	P057	1, €	6,8 €	5, €	T1	C4	05/01/2018	2
180005	P004	2, €	4,35 €	2,5 €	T1	C4	05/02/2018	2
180005	P017	7, €	5,75 €	5, €	T1	C4	05/02/2018	2
180005	P051	7, €	7,02 €	4,5 €	T1	C4	05/02/2018	2

There I have quantity (cantidad), sales prices (precio), cost price (coste) and the rest are just foreing keys for dimensions to slice and dice.

How can I do the budget table, I guess I can do it in excel and including the two budget variables  (quantity and price)? but don't know how to link them to made the DAX formulas easy or maybe is a good idea to combine this fact table with the budget table created in excel using the CODLAL column?. Also I am only interested in monthly comparison, don't need daily details.

Any ideas? I don't have any problem in sharing the .pbix file, because this is dummy data.

Thank you very much and Merry Christmas!!

[v-juanli-msft]

Hi @ContabilidadBI

Does your Bugdet table look like this 

CODLAL	Cantidad	Precio	Coste
180002	9 €	8 €	4, €
180005	13 €	14.20 €	2,5 €
180005	19 €	16 €	4, €
180008	23 €	14.20 €	2,5 €
180008	29 €	26 €	4, €
180011	23 €	24.20 €	2,5 €
180014	23 €	24.20 €	2,5 €
180017	29 €	26 €	4, €
180023	23 €	24.20 €	2,5 €
180020	29 €	26 €	4, €
180026	23 €	24.20 €	2,5 €

For "CODLAL" column in Bugdet, values are distinct in this column as above, right?

If so, you could import this table to Power BI, and create a relationship between this table and sales table.

Best Regards

Maggie

[ContabilidadBI]

Hi @v-juanli-msft,

Thank you for your response. I still don't have a budget table because I don't know the apropiate way to do it in order to achieve what I want. The objective is to be able to compare the actual vs budget (monthly) but being able to differenciate sales price (precio), sales cost (coste) and units sold (cantidad) deviations, in order to see the causes of the deviation in the net result of the period selected by the user. Maybe the company sold less than they thought in the budget, or maybe it's because they sold for cheaper prices that in the budget or maybe because they bought at higher prices than expected in the budget. That's what I would like to be able to show.

So in order to do that I have to include price, units and cost in the budget table also. And if I include other dimensions like color it would be even better, because I could do the budget vs actual analysis explained above, but only for red products for example. That would be perfect!

I have total freedom to do the budget table as I want, because I will do it manually in excel.

Thank you very much for your help guys,

Víctor

[v-juanli-msft]

Hi @ContabilidadBI

Have you ever had problem to create budget tables or compare actual sales and budget sales?

there are some threads regarding the same topic "compare actual and budget with power bi" which you may look into and find something useful.

Actual vs Budget weekly sales

Compare actual with budget in two overate tables

Analyzing Personal Finances with Power BI - Expenses Versus Budget

Dealing with Budget vs Actuals Closed Dates

Best Regards

Maggie

[ContabilidadBI]

Hi @v-juanli-msftMaggie, thanks for your response.

I have checked the links you provided about creating actual vs budget but they are different from what I want, because I need to include in the budget, columns for price, cost and quantity, in order to be able to compare not only total actual sales vs total budget sales but also deviations in price, in cost and in units sold, with formulas like this:

price deviation = ( PriceActual - PriceBudget) * QuantityActual

units deviation = ( QuantityActual - QuantityBudget ) * PriceBudget

So for example, if our sales are 1000 € less than we have (predicted) in the budget, which part is because of a price deviation and which one because a deviation in the quantity of units sold?

Thank you very much.

[ContabilidadBI]

Hi @v-juanli-msftMaggie, thanks for your response.

I have checked the links you provided about creating actual vs budget but they are different from what I want, because I need to include in the budget, columns for price, cost and quantity, in order to be able to compare not only total actual sales vs total budget sales but also deviations in price, in cost and in units sold, with formulas like this:

price deviation = ( PriceActual - PriceBudget) * QuantityActual

units deviation = ( QuantityActual - QuantityBudget ) * PriceBudget

So for example, if our sales are 1000 € less than we have (predicted) in the budget, which part is because of a price deviation and which one because a deviation in the quantity of units sold?

Thank you very much.

[v-juanli-msft]

Hi @ContabilidadBI

Utill now, i don't know what exactly you budget table structures.

If your budget table has distinct "CodProducto" column and budget quantity (cantidad),budget sales prices (precio), budget cost price (coste) assicated with each "CodProducto" ,

then create a relationship between budget table and actual table based on the  "CodProducto" column.

after that, you can use columns from "bugdet table" in "actual table" or use columns from "actual table" in "bugdet table".

Best Regards

Maggie

[ContabilidadBI]

Hi @v-juanli-msft,

I don't have the budget table yet, that's why I have total freedom to do it as I want in excel. My question is what is the best structure to achieve what I want, which is to be able to analize actual vs budget but with sales price, cost price and quantity effects in the deviations from the budget profit/loss. I want to do it. I thought about creating a table with the same structure but with only a few lines per month for different products, colors... I don't want to analyze with a granularity below the month. Is that a good idea?

Thanks

[affan]

Hi @ContabilidadBI

You can create a budget table in excel and then connect it to Power BI. Just an additional note is that you should store this excel file on onedrive, so if you have any budget revisions you can update this file and Power BI online service can refresh this easily.

To create a comparison create a date dimension table. You can also use the date template shred by SQLBI.

Create a relationship of both tables (Fact & budget) with the dates table. Then you can create the desired analysis.

If you need further help, please share the pbix file.

Regards,

Affan 

[ContabilidadBI]

Hi @affan,

thank you for your response. I already have a date dimension in my model, If I create a budget table in excel and import it to Power BI, wouldn't be easier if I combine the budget table with the actual table so I have columns with actual price, actual quantity, budget price, budget quantity... all in the same table?

What I want to do is an analysis of budgetary deviations. For example, if I want to know which part of the revenue deviation is due to a devitation in the price and which one due to a deviation in the quantity sold I can use the following formulas:

price deviation = ( PriceActual - PriceBudget) * QuantityActual

units deviation = ( QuantityActual - QuantityBudget ) * PriceBudget

I can share the pbix file, here you have the link: https://drive.google.com/open?id=1zxL2UEVs-DouL2eNAnnuFxLom5g5vehg

Thank you very much, I really apreciate how much I am learning from you guys here in the forum.