Adding Budget Data from excel spreadsheet & displaying it with actual data from database tables

[AAMW01]

I have a spreadsheet with a list of manual entry budget data which needs to be used by multiple tables from a database. I have a calendar table which links to each database table and the calendar also links to excel spreadsheet budget table. The calendar table acts as a mutual link between them.

Here is an example of the calendar table

Date	Year	Month	Day	FiscalYearNumber	FiscalYearDisplay	FiscalMonth	Fiscal Month Name
01/05/2021	2021	May	1	2020	FY20-21	12	May
01/06/2021	2021	June	1	2021	FY21-22	1	June
01/07/2021	2021	July	1	2021	FY21-22	2	July
01/08/2021	2021	August	1	2021	FY21-22	3	August
01/09/2021	2021	September	1	2021	FY21-22	4	September
01/10/2021	2021	October	1	2021	FY21-22	5	October
01/11/2021	2021	November	1	2021	FY21-22	6	November
01/12/2021	2021	December	1	2021	FY21-22	7	December
01/01/2022	2021	January	1	2021	FY21-22	8	January
01/02/2022	2021	February	1	2021	FY21-22	9	February
01/03/2022	2021	March	1	2021	FY21-22	10	March

Created by:

Calendar Table = ADDCOLUMNS(

CALENDAR(DATE(2003,01,01), DATE(2025,01,01)),

"Year", YEAR([Date]),

"Month Year Num", CONCATENATE(YEAR([Date]), FORMAT([Date],"MMM")),

"Month", FORMAT([Date], "MMM"),

"DAY", FORMAT([Date], "D"),

)

FiscalYearNumber = If( Month([Date]) >= 6 , Year([Date]),Year([Date]) -1 )

FiscalYearDisplay = "FY"&Right(Format([FiscalYearNumber],"0#"),2)&"-"&Right(Format([FiscalYearNumber]+1,"0#"),2)

Fiscal Month Name = SWITCH('Calendar Table'[FiscalMonth],

1, "June", 2, "July", 3, "August", 4, "September", 5, "October", 6, "November", 7, "December", 8, "January", 9, "February", 10, "March", 11, "April", 12, "May")

I then, for example, have a sales table which shows all transactions.

CustomerAccNo	CustomerAccName	TransactionDate	Value	Group
1	Name 1	01/01/2021	50	Type 1
2	Name 2	03/02/2021	120	Type 1
2	Name 2	07/03/2021	44	Type 1
4	Name 4	22/03/2021	123	Type 2
2	Name 2	23/03/2021	33	Type 1
1	Name 1	10/04/2021	88	Type 1
5	Name 5	11/04/2021	180	Type 3
6	Name 6	11/04/2021	32	Type 3
5	Name 5	11/04/2021	5	Type 3

This links to the calendar table on date.

Then in my Budget excel spreadsheet I have a column for SalesType1, SalesType2, SalesType3 along with all other columns for all other tables like Overheads based on certain types etc.... The budgets are monthly which also have a link to the calendar table on date

DateBudgets	SalesBudgetsType1	SalesBudgetsType2	SalesBudgetsType3
01/06/2021	22	50	25
01/07/2021	15	110	50
01/08/2021	25	75	65
01/09/2021	30	120	75
01/10/2021	30	115	100
01/11/2021	5	110	125
01/12/2021	10	100	150
01/01/2022	15	90	175

The only work around I can think of has been creating a column in the connected table (Calendar Table) and have each column representing the budget column where the group type = the budget group type. Here is an example

SalesBudgetType1 = CALCULATE(SUM(Budgets[SalesBudgetType1]),'Sales'[Group] = "Type 1")

I now have a visual  table in power BI using actual figures, it shows for all rows of group types. 

Based on my circumstances can you please assist me on how I have linked the external excel budget data to the actual data tables and if there is a better way to display the results as the column shows incorrect data in rows I don't want, marked as an X. Potentially need an ELSE 0 statement when it doesn't equal the correct Group type? 

Any help appreciated.

[amitchandak]

@AAMW01 , Unpivot the type column in budget , you will get row values in column say type

SalesBudgetsType1 , SalesBudgetsType2, SalesBudgetsType3

replace SalesBudgets with empty space and you will get Type1,Type2 and Type3

You can common group table joining both sales and Budget, along with calendar 

[jdbuchanan71]

@AAMW01 

Adding KEEPFILTERS to your measure should restrict what lines the budget $ is showing on.

SalesBudgetType1 =
CALCULATE (
    SUM ( Budgets[SalesBudgetType1] ),
    KEEPFILTERS ( 'Sales'[Group] = "Type 1" )
)

The other option would be to unpivot the budget data, add the group as a column and create a group table that could link to both your budget and actuals, the same way your date table links to both.

[AAMW01]

The KeepFilters function does not change the outcome. The problem I have is that the sales transactions table do not show future dates, only the calendar table does. If I put the calculation in the sales table, it does not show any values. If I put the calculation in the calendar table, it uses seperate names.

*The title of the first table is the budget data for sales, not actual sales data.

I have unpivoted the tables and now have Date, Attribute & Value. 

[v-kkf-msft]

Hi @AAMW01 ,

Has your problem been solved? If it is solved, please mark a reply which is helpful to you.

If the problem is still not resolved, please see if my test PBIX file is helpful.

 

Best Regards,
Winniz

[AAMW01]

No the issue is still not fixed. 

There are 100,000 rows of data from the sales table where each row is a transaction, connected to the calendar table to then get the sum each month per group type. The budget data is a sum of the monthly figures based on type.  

How would I enter that 3rd linking table? Do I need to make calculated columns and what are the measures I need to create to calculate these. 

I cannot download the file unfortunately

[v-kkf-msft]

Hi @AAMW01 ,

1. According to your model, I unpivoted table Budgets. 

2. In order to create a relationship between the Attribute column of table Budgets and the Group column of Sales Table, I replaced the "SalesBudgetsType" text in the Attribute column with "Type " (note Space). 

3. Close Power Query Editor and apply the changes, then create a calculation table to connect Budgets and Sales Table. 

Type = DISTINCT('Sales Table'[Group])

4. Create relationships between tables (associate with fields of the same color).

You can display sales and budget value in visual.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AAMW01 · Accepted solution]

Thank you for your detailed solution. 

I managed to display the budget data as a DAX function in the sales table:

Sales output forecast = CALCULATE(

SUM('Budgets'[Value]),

FILTER('Budgets',

'Budgets'[GroupType] = MAX('Sales'[Name])

)

)

Not really sure why I needed to use MAX and why I couldn't just use 'Budgets'[GroupType] = 'Sales'[Name]. 

EDIT: see reply from @v-kkf-msft who explains why.

Either way this displays the correct data from the budgets table relating to the group type and date.

I then use date filters to view by certain dates.

[v-kkf-msft]

Hi @AAMW01 ,

The expression 'Budgets'[GroupType] = 'Sales'[Name] has two column references ('Budgets'[GroupType] and 'Sales'[Name]) which cannot be evaluated because the measure does not provide a row context. 

The row context is available in FILTER function. So you need to specify an aggregation such as min, max for 'Sales'[Name] to get a single result.

You can also use  'Budgets'[GroupType] = SELECTEDVALUE( 'Sales'[Name] ). SELECTEDVALUE returns the value when the context for columnName has been filtered down to one distinct value only.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AAMW01]

Thanks for the information!

Yes I am getting the correct results from my DAX function. 

[jdbuchanan71]

Now you will need to add a type table to your model that links to both of your fact tables.  Then in your visual pull the type from the type table and it will filter both the sales and budget tables the same way the date table filters both.