Suggest the data model for my data

[rajanimaddala]

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

[v-chuncz-msft]

@rajanimaddala 

You may take advantage of Use drillthrough.

[rajanimaddala]

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,

Product	Sales	Target	Actual Target
Cycles	87000	100000	70000
Bikes	25000	100000	15000
Cars	40000	100000	15000
	152000	100000	100000

Why am I not getting correct Target value?

Cheers

[vivran22]

@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.

[rajanimaddala]

Hi

Thank you for the reply.

I've changed the filter direction to single from Sales to Date and Date to Target

Actually, there is no direct relationship between Sales and Targets unless Product. But, it should be Many-to-Many

I am confused how to create measure to get the trget value.

If I directly pull Target_Value from Targets table and Product and Sales_Amount from Sales table, I am getting the target_value as explained in my previous post.

Do I need to write DAX expression for Target_Value? If so, in which table I need to create measure?

What could be the DAX expression?

Please advise me

Cheers

[vivran22]

@rajanimaddala 

Hi,

Has this been resolved?

Rgds,

Vivek

[rajanimaddala]

Hi Vivek,

   Yes, it worked.

Thanks

[vivran22 · Accepted solution]

@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.