cancel
Showing results for
Did you mean:
Frequent Visitor

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

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 ... ... ... ...
1 ACCEPTED SOLUTION
Community Support

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.

2 REPLIES 2
Community Support

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.

Super User

@welyson205 , 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

Can you share a sample output with example

Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
!! Subscribe to my youtube Channel !!

Announcements

#### The Power BI Community Show

Shabnam Watson demos Incremental refresh & Hybrid Tables and Leila Etaati demos Charticulator.

#### Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors