Forum Discussion
Adding Budget Data from excel spreadsheet & displaying it with actual data from database tables
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.
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
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
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,
WinnizIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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.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,
WinnizIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.