Builiding model relationships

[diana66]

Hello,

I have the following two tables from a database and I am struggling to create a Power BI model:

The data is splited like this:

Table 1:

Time period --> Month and Year

Main category (can be food or non-food)

--> Second Main Category ( each Main Category has 2 or 3 splits, ex: Food splits in Food Beverages and Food Normal)

--> Market (here i have the market where the category has been sold, ex: Hole country, Store 1, Store 2 etc)

--> Fact (is splited in Sales Value and Sales Volume)

--> Category(ex: Dairy)

--> Subcategory (Each Category is splited into one or more Subcategories, ex. Dairy milk, Dairy Youghurt etc)

--> Supplier (each subcategory has many suppliers)

--> Brand (each suppliers has one or more brands)

--> Period 1 and Period 2 are 2 distinct time period measures (ex. Period 1=6 months until now, Period 2= 12 month until now) and I have values for This Year and last year (that means: period 1 This year for time period "November 2019" means cumulated data for May - November 2019), period 1 last year means cumulated data for May - November 2018). Period 2 This Year means cumulated data for December 2018 - November 2019. And in this columns I have values for Sales in Value and in Volume.

Ex: For brand Apple let's say I will have the following

In Table 2 I have the same structure, just that it is for the Product Level.

I have to make an analysis for the development compared to last year for subcategory, supplier, brand and product where I have to calculate Value Change compared to Last Year (for both period 1 and period 2), Value Change % compared to last year, etc. And it has to be for each subcategory, its suppliers, their brands and the brand's products for each store. I will have to have a slicer for Time period, to be able to choose between Period 1 or Period 2, Fact (to choose between Sales or Volume Value, Main Category, Second Main Category, Category and Subcategory.

I was thinking about creating a hierarchy Category --> Subcategory --> Supplier --> Brand --> Product and then to create measures and make a matrix with the hierachy, market and measures. But I have to create a relationship between the tables and I have so many columns with repeated values and I don't know how to do it :(. 

Could you, please, help me?

Thank you!

[diana66]

Hi, 

Thank you so much for the suggestion. I managed to adjust the database, in order to be in a more clear format. Now it looks like this:

I will post also a sample from the second table when I will have the new format.

In the report I have to calculate some changes, like in the below example:

And when I will receive the table containing product data I would like to be able to drill down in the report matrix like this Subcategory--> Supplier --> Brand --> Product. This is why I think I have to connect the tables. 

[Anonymous]

Hi @diana66,

Your scenario seems not suitable to use the relationship to link tables, there are too many relationships fields. For power bi model design, normally use one major relationship to analysis across different tables.

I'd like to suggest you extract similar fields and union two table records. (you can add a new field to mark which table these records from) Then you can simply write measure formulas to compare between two table records.

Relationships in analysis services tabular models  

Union two tables with different columns and sum the same register 

Regards,

Xiaoxin Sheng

[Greg_Deckler]

Difficult to fully visualize your data in my head, perhaps sample data for the second table would help. Couple recommendations, I would unpivot your Period # this year columns.

Create a Date/Calendar table using CALENDAR or CALENDARAUTO. Create Brand, Category, Subcategory and Product tables using Table = DISTINCT('Table'[Column])

Relate all of your dimension columns to your fact tables in a star schema.