Forum Discussion

AAMW01's avatar
AAMW01
Icon for Helper I rankHelper I
5 years ago
Solved

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

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 als...
Need Help
  • AAMW01's avatar
    AAMW01
    5 years ago

    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.
jdbuchanan71's avatar
jdbuchanan71
Icon for Super User rankSuper User
5 years ago

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.