Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

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 @Anonymous 

 

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 @Anonymous 

 

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

@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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.