locked
Actual table and budget table RRS feed

  • Question

  • Hello. I have an actuals table that is identical to a budget table. I'm creating formulas for both the actual table and the budget table. Each table has the SAME categories. The problem is, however, that I can't make comparisons in the same table because the row labels (the categories) are coming from different tables (even though they are the same) and it says relationship needed and just returns all the same values. What should I do? Thanks. 
    Monday, October 31, 2016 6:10 PM

Answers

  • Just saw that there was no budget-table in it, so here comes the mockup (the budget-table is connected to the other tables just like the actual-table): & again: Don't take any fields from your data tables into the pivot-table!!


    Imke Feldmann TheBIccountant.com

    • Marked as answer by AlexMartini Wednesday, November 2, 2016 1:20 AM
    Tuesday, November 1, 2016 5:08 PM
    Answerer

All replies

  • I guess it's the same as in your other post: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/0f90c472-518d-44b9-9c50-e4382f253c31/relationships-needed-but-doesnt-make-sense?forum=sqlkjpowerpivotforexcel

    Please provide some sample data and the expected result.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Monday, October 31, 2016 6:41 PM
  • Ok. Here you go. I have two tables, Actuals and Budget. They both have the same type of information and the same categories. I want to sort actuals and budget measurements by the same categories (and line items). As it is now I have to create two seperate pivot tables. Here is a picture of the setup. 
    Monday, October 31, 2016 11:11 PM
  • In your model pane, right click on the category column, you will have a rename menu option. rename this column something different and use this in your comparisons.
    Monday, October 31, 2016 11:27 PM
  • Key is to create your pivot-tables with fields from your lookup-tables only. My recommendation is to hide your data-tables completely, in order to prevent these mistakes.

    You might find some useful further information on this topic here: http://exceleratorbi.com.au/the-optimal-shape-for-power-pivot-data/ (can highly recommend the other articles there as well).


    Imke Feldmann TheBIccountant.com

    Tuesday, November 1, 2016 6:42 AM
    Answerer
  • I'm not sure what either of you two are talking about ahah. I need to connect the two tables. They essentially have the same structure but one is actuals and the other is budget. I don't believe that renaming a column is going to help with a disconnected table issue. For example, I have sum for actuals and sum for budget, and I want to look at them on the same pivot with the same categories (since the categories are the same for both tables). 
    Tuesday, November 1, 2016 4:33 PM
  • Alex, I cannot explain it better than Matt did in his article.

    Imke Feldmann TheBIccountant.com

    Tuesday, November 1, 2016 4:48 PM
    Answerer
  • Just saw that there was no budget-table in it, so here comes the mockup (the budget-table is connected to the other tables just like the actual-table): & again: Don't take any fields from your data tables into the pivot-table!!


    Imke Feldmann TheBIccountant.com

    • Marked as answer by AlexMartini Wednesday, November 2, 2016 1:20 AM
    Tuesday, November 1, 2016 5:08 PM
    Answerer