Help with data model and DAX

[Tarek78]

Hi Everyone,

I am a begginner in Power Pivot. I have a simple data model with three tables as follows:

Table PERIOD: a simple table with daily dates (dates, corresponding month, year, day)

Table PROMOTION: this table shows the product codes on promotion each month of the year

> Month 1 (January) > Product Code 02 (Jeans)

> Month 1 (January) > Product Code 03 (T-Shirt)

> Month 2 (February) > Product Code 01 (Pants)

etc.

Table FACT: showing the data I extract from a software with all the information as below :

- Month Number

- Year

- Product Code (01,02,03)

- Sales

- Sales Type (Promotions, Online, On Store)

I would like to build a simple data model in order to calculate the sales per product code for each month and determine, out of the total sales, how much was realized on the product that were on a promotion on a particular month

example if report of January where Jeans and T-Shirt are on promotions

- Product 01 ( Jeans ) total sales = 100 of which 10 where made on promotions (sales type = promotion)

- Product 02 ( T Shirt ) total sales = 200 of which 30 where made on promotions (sales type = promotion)

- Product 03 ( Pants ) total sales = 500 no promotions on Jeans in January.

Therefore, a total sales of 800 of which 40 of promotions

I would like to do the same each month.

Thanks,

[Tarek78]

Hi @Anonymous ,

Thanks for your answer and sorry for not being clear. You can see attached a picture of the current model.

Each month, we put some products on promotions. We would like to measure the performance of the promotional campaign each month. For example, in April, product code 001 which is Jeans are on promotion.

Each month, I would like to see the total sales generated of the product codes on promotion. This way, I could calculate a ratio showing: Total sales from promotion / Total sales.

Example: in April, we have 3 products on promo (jeans, shirts, shoes).

Jeans: turnover of 100 of which 10 was made on promotion > promotion ratio of 10%

Shirt: turnover of 200 of which 10 was made on promotion > promotion ratio of 5%

Shoes: turnover of 500 of which 250 was made on promotion > promotion ratio of 50%

Total sales of products on promotion in April: turnover of 800 of which 270 was made on promotion > promotion ratio of 33.75%.

I hope it is more clear?

[Anonymous]

Hi @Tarek78 ,

I'm a little confused about your needs, Could you please explain them further? It would be good to provide a screenshot of the results you are expecting and desensitized example data.

Thanks for your efforts & time in advance.

Best regards,
Community Support Team_Binbin Yu