What can I do with a table with discount measures grouped by time slot?

[Anonymous]

Hi everyone, any suggestion is very welcome.

I have two discount tables, one that has a time range (07:00, 08:00, ...) and the average discount applied to products over a period of time (130%, 120%). And the other table with sales reports.

My question is I have no idea what should be done with the average discount table in my analyses. I tried to get the discount per sale by dividing the total sales in a certain range by the percentage of the average discount, these values ​​are observed in the column in red.

The sales table is not complete.

DISCOUNT TABLE

TIME_RANGE	RATE	DISCOUNT_BY_PRODUCT
07:00	130%	10%
08:00	130%	6%
09:00	120%	6%
10:00	120%	6%
11:00	100%	11%
12:00	90%	16%
13:00	70%	27%
14:00	50%	37%

SALES TABLE

DATE	ID_PRODUCT	SALES_VALUE	TIME_RANGE
01/08/2021	6	25,25	10:00
01/08/2021	3	25,4	7:00
01/08/2021	13	15,38	13:00
01/08/2021	5	26,88	14:00
01/08/2021	6	8,37	11:00
01/08/2021	7	18,22	12:00
01/08/2021	9	26,12	13:00
01/08/2021	10	2,42	10:00
01/08/2021	7	1,71	13:00
01/08/2021	4	13,03	13:00
01/08/2021	3	20,72	14:00
01/08/2021	5	22,33	9:00
01/08/2021	13	23,05	14:00
...	...	...	...

[v-jingzhang · Accepted solution]

Hi @Anonymous 

You can do as Amit suggested. First create a one-to-many relationship on TIME_RANGE columns between two tables. 

Then create two new columns in Sales table. 

Discount Rate = RELATED('DISCOUNT TABLE'[RATE])

discount per sale = DIVIDE('SALES TABLE'[SALES_VALUE],'SALES TABLE'[Discount Rate])

If this is not what you want, can you share some expected output?

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[amitchandak]

@Anonymous , Not very clear. If we can create a 1-M join between table 1 and table2 [sales], then we can use related(Table1[Discount_by_product])  in a column or measure 

or refer 4 ways to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8

Can you share a sample output with example