none
Table structure and relationships

    Question

  • Hi all,

    I need to develop a dashboard where to compare the following type of data:

    • Sales plan (info provided by month)
    • Sales actuals (info provided by quarter)
    • Sales goal (info provided by quarter).

    Also, this information is provided by business category and region and includes the revenue and units.

    What I did was to create two tables, the first one to store the information by month and the other one by quarter. Now I can't find a way to relate them since creating keys based in business category, month (in the quarter table, I created a calculated field that assign a month date depending on the Q), region and type of data produces a key that won't find a match in the other table (because of the type). 

    Example of table 1

    Business Period Type Region Revenue Units
    Jeans 01/11/2012 Plan 1 South 15000 22500
    Tops 01/11/2012 Plan 1 North 40500 60750
    Shoes 01/11/2012 Plan 1 East 30000 45000
    Jeans 01/11/2012 Plan 2 South 12000 18000
    Tops 01/11/2012 Plan 2 North 38000 57000
    Shoes 01/11/2012 Plan 2 East 27000 40500

    Example of table 2:

    Business Period Type Region Revenue Units Date (Calculated based on Q)
    Jeans Q113 Goal South 20000 30000 01/11/2012
    Tops Q113 Goal North 45000 67500 01/11/2012
    Shoes Q113 Goal East 35000 52500 01/11/2012
    Jeans Q412 Actual South 19000 28500 01/08/2012
    Tops Q412 Actual North 43000 64500 01/08/2012
    Shoes Q412 Actual East 29000 43500 01/08/2012


    I will need to make comparisons of sales plans vs goals and actuals of their respective Period of time. What do you suggest?

    Thank you for your time

    Saturday, April 20, 2013 4:49 PM

Answers

  • Hi,

    If what you want to do is create a basic relationship so you can report on a quarterly basis then all you need is an external date table that has a column with unique, contiguous dates for the period in question along with columns for quarters (and anything else required). You can then relate the date column in each of your fact tables to your date table and if you drag in the Quarter column to your pivot then you will see the desired result.

    In some circumstances it also makes sense to break down a quarterly number to report on a monthly basis - normally it would make more sense for the actual to be at the lower granularity with the comp at a quarterly level but maybe that's what you are trying to do. If so a measure such as this will help:

    =IF(HASONEVALUE(DimDate[Month]),
           CALCULATE(SUM(FactB[Revenue]),
                                         ALL(DimDate[Date],DimDate[Month]),
                                         FactB[Type]="Goal")/3,
           CALCULATE(SUM(FactB[Revenue]),FactB[Type]="Goal")
          )

    Hope this makes sense, I saved my 2 minute test model here for reference.

    Jacob


    Sunday, April 21, 2013 5:20 AM