Forum Discussion

rajanimaddala's avatar
rajanimaddala
Helper II
7 years ago
Solved

Suggest the data model for my data

Hi mates,

I need to create a dashboard for sales.

I have different tables in the database to hold sales for different products. Each comes from different systems.

There is no direct relationship between these. For example, particular product sales value is calculated by SUM(value from table1 and value from table2 - value from table3)

I can create a UNION query to get the aggregated value but I need to show the underlying data when the users clicks on Show Data.

Columns in all the tables are not same.

I am struggling to create a data model for this. Any suggestion please

Regards

Need Help
  • vivran22's avatar
    vivran22
    7 years ago

    rajanimaddala 

     

    In such cases, I usually create a simple table of unique products and then link it with Product and Target. This table then will act as filter table and you can use it in your visuals and DAX.

     

    So you will have two filter tables: Product(Unique) & Calendar/Date table

     

    And you will have two fact tables: Performance Data and Target Data

     

    There will be a Many-to-One relationship between fact and filter tables and then you can write your DAX suitable to your need. You can use RELATED function to filter the facts table.

     

    Hope this should help.

     

    Rgds,

    Vivek

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

7 Replies

    • rajanimaddala's avatar
      rajanimaddala
      Helper II
      7 years ago

      Hi

      Thanks for the reply.

      Let me explain my model

      I have 3 tables

      1. Sales (Product, Transaction_Date, Sales_Amount)

      2. Targets(Product, Transaction_Date(last day of month), Target_Value)

      3. Date(Date, FY, Qrtr, MonthName, Year)

       

      I have created relationship (filter direction = Both)from Sales to Date and Targets to Date(Filter direction=Both)

      I am just showing the data in a table like ProductName, MonthName, Year, Sales_Amount, Target_Value

      Everything is fine with Sales but Targets are aggregated to whole products

      Example,

      ProductSalesTargetActual Target
      Cycles8700010000070000
      Bikes2500010000015000
      Cars4000010000015000
       152000100000100000

       

      Why am I not getting correct Target value?

      Cheers

      • vivran22's avatar
        vivran22
        Community Champion
        7 years ago

        rajanimaddala 

         

        Hi,

         

        The relationship between Target and Sales table is missing hence you are not getting the filtered values for Target.

         

        Also, in my experience, single filter direction works more effeciently. Ideally, you will use Date table to filter the Sales and Target table and not vice versa.

         

        Rgds,

        Vivek

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.