Daily Transactions

[alexcatala]

Hi,

I am looking to extract a daily figure from the weekly figure but divide it accordingly to their daily sales. Meaning if they had better sales 1 day, that day should have higher transactions than the others.

Weekly figures:

Week 1                                from 01-to 07

Store	Transaction
021-IRVINE SPECTRUM	120
022-THE OUTLETS AT O	210
051-LAS AMERICAS	89

Daily sales:

Store	date	Price
021	01/02/21	1400
021	02/02/21	1500
021	03/02/21	900
021	04/02/21	1600
021	05/02/21	1000
021	06/02/21	1200
021	07/02/21	1500

I hope it makes sense.

I want that based on the sales of the day, the transaction might increase or decrease, meaning days with lower sales will have lower transactions and days with higher sales higher transaction. IT is like making the division of the weekly transaction into daily, but taking into consideration the daily sales, in order to give a higher number of transaction of lower depending on their daily sales. 

EX:

Transactions weekly 210

                Sales             Transactions              Store

Monday         2000             30                          022

Tuesday         1500             20                          022

Wednesday  1500             20                            022

Thursday        2000            30                           022

Friday             2000            30                           022

Saturday        3000             40                           022

Sunday          3000             40                           022

Thanks for your help

[v-easonf-msft · Accepted solution]

Hi, @alexcatala 

I'm not certain that I'm understanding you correctly.

So I make a pbix file for reference.

Steps:

1.use function "lookupvalue" to create a calculated column in table 'Daily Sales'

Total Transactions = LOOKUPVALUE('Weekly figure'[Transaction],'Weekly figure'[Store],'Daily sales'[Store])

2.Calculate the percentage of daily sales in a weekly sales

Percentage = 
SELECTEDVALUE ( 'Daily sales'[Sales] )
    / CALCULATE (
        SUM ( 'Daily sales'[Sales] ),
        ALLEXCEPT ( 'Daily sales', 'Daily sales'[Store] )
    )

3.calculate transaction  depending on the proportion of daily sales. 

Daily Transactions = SELECTEDVALUE('Daily sales'[Total Transactions])*[Percentage]

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.