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.

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

    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. 

    • jdbuchanan71's avatar
      jdbuchanan71
      Icon for Super User rankSuper User
      5 years ago

      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.

    • v-kkf-msft's avatar
      v-kkf-msft
      Icon for Community Support rankCommunity Support
      5 years ago

      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's avatar
        AAMW01
        Icon for Helper I rankHelper I
        5 years ago

        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