Forum Discussion

alexcatala's avatar
alexcatala
Helper IV
5 years ago
Solved

Transactions Daily from Weekly figure

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

StoreTransaction
    021-IRVINE SPECTRUM120
    022-THE OUTLETS AT O110
    051-LAS AMERICAS    89

 

Daily sales:

StoredatePrice
    02201/02/211400

022

02/02/211500

022

03/02/21900

022

04/02/211600

022

05/02/211000

022

06/02/211200

 022

07/02/211500

 

I hope it makes sense.

 

Thanks for your help

Need Help
  • alexcatala's avatar
    alexcatala
    5 years ago

    Anonymous 

     

    Hi,

     

     

    I have been trying to use your formula but it doesn't allow me to formulate it the way you have it.

     

    Firstly, in the 1st calculate, it doesn't allow me to just add f_DailySales[Store], I had to add a * as it doesn't recognise. If I remove the * this is what appears:

     

     

    Secondly, on the 2nd Calculate, it doesn't recognise either the F_DailySales[Store]. Even adding * it doesn't recognise as the filter only will read the column Transaction combined, no the other column, F_DailySales.

     

    Any suggestion?

     

    Thanks a lot for your help

  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi alexcatala 

    The dax I shown you before is in calculated column.

    In your screenshot, you may want to build a measure.

    "Earlier" function couldn't be used in measure, we need to use aggregation in measure by function "Sum" or "Max".

    You see in my sample , if I build a measure by this dax it will show error as well.

    Try this measure.

    Daily Transactions in Measure = 
VAR _Sum =
    CALCULATE (
        SUM ( 'Daily sales'[Price] ),
        FILTER (
            ALL('Daily sales'),
            'Daily sales'[Store] = MAX( 'Daily sales'[Store] )
                && 'Daily sales'[Year] = MAX ( 'Daily sales'[Year] )
                && 'Daily sales'[WeekNum] = MAX ( 'Daily sales'[WeekNum] )
        )
    )
VAR _PERCENT =
    DIVIDE ( SUM('Daily sales'[Price]), _Sum )
VAR _Trabsaction =
    CALCULATE (
        SUM ( 'Weekly figures'[Transaction] ),
        FILTER (
            ALL('Weekly figures'),
            'Weekly figures'[Store] = MAX ( 'Daily sales'[Store] )
                && 'Weekly figures'[Year] = MAX ( 'Daily sales'[Year] )
                && 'Weekly figures'[WeekNum] = MAX( 'Daily sales'[WeekNum] )
        )
    )
RETURN
    _Trabsaction * _PERCENT

    Result:

    For more details about the differences between calculated column and measure you may refer to this blog:  UNDERSTANDING THE DIFFERENCES BETWEEN CALCULATED COLUMNS & MEASURES IN POWER BI

    You can download the pbix file from this link: File

     

    Best Regards,

    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

8 Replies

    • alexcatala's avatar
      alexcatala
      Helper IV
      5 years ago

      parry2k 

       

      Hi,

       

      I am trying to obtain a daily transaction from the weekly figure.

       

      I have seen It can easily make an average, but not what I really want.

      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

      Monday         2000             30

      Tuesday         1500             20

      Wednesday  1500             20

      Thursday        2000            30

      Friday             2000            30

      Saturday        3000             40

      Sunday          3000             40

       

      I hope it make sense now

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi alexcatala 

    I think you want to calculate the daily Transaction for each store for each week.

    So you need to add a weeknum column into your table. If you have values in different years, you need to add a year column as well.

    My Sample:

    Weekly Figure

    From 01-to 07 in Feb so the weeknum is 6 in 2021.

    Daily sales:

    Year and WeekNum are all calculated column.

     

    Year = YEAR('Daily sales'[date])
    WeekNum = WEEKNUM('Daily sales'[date],2)

     

    Then calculate the daily Transaction by calculated column.

     

    Daily Transactions =
VAR _Sum =
    CALCULATE (
        SUM ( 'Daily sales'[Price] ),
        FILTER (
            'Daily sales',
            'Daily sales'[Store] = EARLIER ( 'Daily sales'[Store] )
                && 'Daily sales'[Year] = EARLIER ( 'Daily sales'[Year] )
                && 'Daily sales'[WeekNum] = EARLIER ( 'Daily sales'[WeekNum] )
        )
    )
VAR _PERCENT =
    DIVIDE ( 'Daily sales'[Price], _Sum )
VAR _Trabsaction =
    CALCULATE (
        SUM ( 'Weekly figures'[Transaction] ),
        FILTER (
            'Weekly figures',
            'Weekly figures'[Store] = EARLIER ( 'Daily sales'[Store] )
                && 'Weekly figures'[Year] = EARLIER ( 'Daily sales'[Year] )
                && 'Weekly figures'[WeekNum] = EARLIER ( 'Daily sales'[WeekNum] )
        )
    )
RETURN
    _Trabsaction * _PERCENT

     

    Result is as below.

     

    Best Regards,

    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

    • alexcatala's avatar
      alexcatala
      Helper IV
      5 years ago

      Anonymous 

       

      Hi,

       

       

      I have been trying to use your formula but it doesn't allow me to formulate it the way you have it.

       

      Firstly, in the 1st calculate, it doesn't allow me to just add f_DailySales[Store], I had to add a * as it doesn't recognise. If I remove the * this is what appears:

       

       

      Secondly, on the 2nd Calculate, it doesn't recognise either the F_DailySales[Store]. Even adding * it doesn't recognise as the filter only will read the column Transaction combined, no the other column, F_DailySales.

       

      Any suggestion?

       

      Thanks a lot for your help

    • alexcatala's avatar
      alexcatala
      Helper IV
      5 years ago

      Anonymous 

       

      Hi,

       

      I found 2 areas where it doesn't work your formula.

       

       

      In the 1st Calculate, I don't know why it doesn't read the column. 

      On the 2nd Calculate, it doesn't read either but as the filter is using the column Transactions, it won't read the other column( DailySales).

       

      Any suggestion?

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi alexcatala 

        The dax I shown you before is in calculated column.

        In your screenshot, you may want to build a measure.

        "Earlier" function couldn't be used in measure, we need to use aggregation in measure by function "Sum" or "Max".

        You see in my sample , if I build a measure by this dax it will show error as well.

        Try this measure.

        Daily Transactions in Measure = 
VAR _Sum =
    CALCULATE (
        SUM ( 'Daily sales'[Price] ),
        FILTER (
            ALL('Daily sales'),
            'Daily sales'[Store] = MAX( 'Daily sales'[Store] )
                && 'Daily sales'[Year] = MAX ( 'Daily sales'[Year] )
                && 'Daily sales'[WeekNum] = MAX ( 'Daily sales'[WeekNum] )
        )
    )
VAR _PERCENT =
    DIVIDE ( SUM('Daily sales'[Price]), _Sum )
VAR _Trabsaction =
    CALCULATE (
        SUM ( 'Weekly figures'[Transaction] ),
        FILTER (
            ALL('Weekly figures'),
            'Weekly figures'[Store] = MAX ( 'Daily sales'[Store] )
                && 'Weekly figures'[Year] = MAX ( 'Daily sales'[Year] )
                && 'Weekly figures'[WeekNum] = MAX( 'Daily sales'[WeekNum] )
        )
    )
RETURN
    _Trabsaction * _PERCENT

        Result:

        For more details about the differences between calculated column and measure you may refer to this blog:  UNDERSTANDING THE DIFFERENCES BETWEEN CALCULATED COLUMNS & MEASURES IN POWER BI

        You can download the pbix file from this link: File

         

        Best Regards,

        Rico Zhou

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.