cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
welyson205
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_RANGERATEDISCOUNT_BY_PRODUCT
07:00130%10%
08:00130%6%
09:00120%6%
10:00120%6%
11:00100%11%
12:0090%16%
13:0070%27%
14:0050%37%

SALES TABLE

DATEID_PRODUCTSALES_VALUETIME_RANGE
01/08/2021625,2510:00
01/08/2021325,47:00
01/08/20211315,3813:00
01/08/2021526,8814:00
01/08/202168,3711:00
01/08/2021718,2212:00
01/08/2021926,1213:00
01/08/2021102,4210:00
01/08/202171,7113:00
01/08/2021413,0313:00
01/08/2021320,7214:00
01/08/2021522,339:00
01/08/20211323,0514:00
............
1 ACCEPTED SOLUTION
v-jingzhang
Community Support
Community Support

Hi @welyson205 

 

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

22012003.jpg

 

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])

22012004.jpg

 

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.

View solution in original post

2 REPLIES 2
v-jingzhang
Community Support
Community Support

Hi @welyson205 

 

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

22012003.jpg

 

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])

22012004.jpg

 

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
Super User
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
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8

 

Can you share a sample output with example

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
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 !!

Helpful resources

Announcements
Power BI Show Ep 4 Post Show Carousel.jpg

The Power BI Community Show

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

PBI April Release 2022 768x460.png

Check it out!

Click here to read more about the April 2022 updates!

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

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