Forum Discussion

alexcatala's avatar
alexcatala
Helper IV
5 years ago

LFL Conversion from 2 different tables

Hi, 

 

I am currently working on a formula to update my LFL Conversion.

 

The problem I am finding is the current formula there is only 1 (Weekly figures) table where I extract the Transactions and also the Footfall.

Now on the new one, I would like to have it from 2 different Tables: Transactions will be from the table (DailyPlan) and Footfall from (DailyFootfall).

 

Here there is a sample of data from DailyPlan

Here there is a sample of data from DailyFootfall

 

How I can change the 1st part of the formula, as Addcolumns only allows me to take from 1 table?

 

VAR BoP_Date_Fixed = [BoP Date]
VAR f_WeeklyFigures_Enriched =
ADDCOLUMNS (
FILTER(f_WeeklyFigures,f_WeeklyFigures[Measure] = "Transaction count"),
"Include", MAX (
[COMP_Active] * ( [OpeningDateAniversary] <= BoP_Date_Fixed ),
[NEW_Active] * ( [OpeningDateAniversary] > BoP_Date_Fixed )
)
,"Footfall",
VAR LY_SKWeek = RELATED((d_DateTable[Year_WeekNum]))
VAR ItStore = f_WeeklyFigures[Store]
Return
CALCULATE ( SUM ( f_WeeklyFigures[Value] ),
ALL ( f_WeeklyFigures ),
ALL ( d_DateTable ),
f_WeeklyFigures[Measure] = "Footfall TY",
f_WeeklyFigures[Store] = ItStore,
d_DateTable[Year_WeekNum] = LY_SKWeek
)
)
VAR f_WeeklyFigures_Enriched_Filtered =
Filter(f_WeeklyFigures_Enriched, [Include] = 1)
VAR Transactions =
SUMX (
f_WeeklyFigures_Enriched_Filtered,
if([value]<>0 && [Footfall]<>0,[value])
)
VAR Footfall =
SUMX (
f_WeeklyFigures_Enriched_Filtered,
if([value]<>0 && [Footfall]<>0,[Footfall])
)
VAR Result = DIVIDE(Transactions,Footfall)
RETURN
IF ( Result = 0, [ND_Sign], Result )

 

 

Any suggestion?

Need Help

8 Replies

  • alexcatala's avatar
    alexcatala
    Helper IV
    5 years ago

    Hi, 

     

    I am currently working on a formula to update my LFL Conversion.

     

    The problem I am finding is the current formula there is only 1 (Weekly figures) table where I extract the Transactions and also the Footfall.

    Now on the new one, I would like to have it from 2 different Tables: Transactions will be from the table (DailyPlan) and Footfall from (DailyFootfall).

     

    Here there is a sample of data from DailyPlan

     

    Here there is a sample of data from DailyFootfall

     

     

    How I can change the 1st part of the formula, as Addcolumns only allows me to take from 1 table?

     

    VAR BoP_Date_Fixed = [BoP Date]
    VAR f_WeeklyFigures_Enriched =
    ADDCOLUMNS (
    FILTER(f_WeeklyFigures,f_WeeklyFigures[Measure] = "Transaction count"),
    "Include", MAX (
    [COMP_Active] * ( [OpeningDateAniversary] <= BoP_Date_Fixed ),
    [NEW_Active] * ( [OpeningDateAniversary] > BoP_Date_Fixed )
    )
    ,"Footfall",
    VAR LY_SKWeek = RELATED((d_DateTable[Year_WeekNum]))
    VAR ItStore = f_WeeklyFigures[Store]
    Return
    CALCULATE ( SUM ( f_WeeklyFigures[Value] ),
    ALL ( f_WeeklyFigures ),
    ALL ( d_DateTable ),
    f_WeeklyFigures[Measure] = "Footfall TY",
    f_WeeklyFigures[Store] = ItStore,
    d_DateTable[Year_WeekNum] = LY_SKWeek
    )
    )
    VAR f_WeeklyFigures_Enriched_Filtered =
    Filter(f_WeeklyFigures_Enriched, [Include] = 1)
    VAR Transactions =
    SUMX (
    f_WeeklyFigures_Enriched_Filtered,
    if([value]<>0 && [Footfall]<>0,[value])
    )
    VAR Footfall =
    SUMX (
    f_WeeklyFigures_Enriched_Filtered,
    if([value]<>0 && [Footfall]<>0,[Footfall])
    )
    VAR Result = DIVIDE(Transactions,Footfall)
    RETURN
    IF ( Result = 0, [ND_Sign], Result )

     

     

    Any suggestion?

  • v-xiaotang's avatar
    v-xiaotang
    Community Support
    5 years ago

    Hi alexcatala 

    Which column do you want to get from the two tables and how do you want to combine them?

    by the way, the syntax of your current formula is wrong. then, I also find tutorials for you, see

    https://docs.microsoft.com/en-us/dax/addcolumns-function-dax

    https://docs.microsoft.com/en-us/dax/selectcolumns-function-dax

    https://docs.microsoft.com/en-us/dax/summarize-function-dax

    https://blog.crossjoin.co.uk/2015/06/01/using-selectcolumns-to-alias-columns-in-dax/

     

     

    Best Regards,

    Community Support Team _ Tang

    If this post helps, 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

      v-xiaotang 

       

      I want to extract the Transactions (column named "Attribute" rows named "Trans") with the value (in the column named "Value") both are in Table named (DailyPlan)

       

      For Footfall I want to extract the value from column "Traffic" in the Table named "DailyFootfall"

       

      After Divide Trans/Footfall to create a percentage.

       

      For both of them be able to make an LFL being able to filter them by Store and dates

      [COMP_Active] * ( [OpeningDateAniversary] <= BoP_Date_Fixed ),
      [NEW_Active] * ( [OpeningDateAniversary] > BoP_Date_Fixed )
      )
       
      I don't know if these dates format is correct but well. 
       
      Let me know the best option to create this...
       
      Thank you so much for your help.Much appreciated
  • v-xiaotang's avatar
    v-xiaotang
    Community Support
    5 years ago

    Hi alexcatala 

    thank you for your reply.

    Is there a relationship between the two tables? if you want to combine 2 columns from 2 tables, you need to create a relationship between them. 

    -

    You can get the effect you want in PQ editor, e.g.

    then you can filter the column

    remove unuseful columns,

    -

    if you just want to combine 2 columns by DAX:

    try 

    Table = ADDCOLUMNS(Table1,"s1",CALCULATE(MIN(Table2[Traffic])))

    then, you can remove columns unuseful.

    FYI:

    https://docs.microsoft.com/en-us/powerquery-m/table-functions

     

     

    Best Regards,

    Community Support Team _ Tang

    If this post helps, 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


      v-xiaotang 

       

      Thanks for your reply.

       

      No there is no relationship between them as they are different topics. The only thing in common will be the store and date.

       

      What I am trying to make is a formula to obtain the Conversion ( Transactions/Footfall) as they are coming from different tables, I would like to know the best way to create a formula to obtain the LFL of this division.

       

      It might be my fault I didn't explain it correctly, but it might be even easier than you think. 

       

      It is a basic division, but each of them come from different tables, but what i am struggling with is the create the LFL, as the previous formula was created with the addcolumns with only 1 table,but now it comes from 2 tables. 

       

      So i am just trying to find out a way to replace the top of the formula to make the formula correct.

       

      This is my latest attempt but it didn't work.

       

      VAR BoP_Date_Fixed = [BoP Date]
      VAR f_WeeklyFigures_Enriched =
      ADDCOLUMNS (
      FILTER(f_DailyPlan,f_DailyPlan[Attribute] = "Trans"),
      "Include", MAX (
      [COMP_Active] * ( [OpeningDateAniversary] <= BoP_Date_Fixed ),
      [NEW_Active] * ( [OpeningDateAniversary] > BoP_Date_Fixed )
      )
      ,"Footfall",
      VAR LY_SKWeek = RELATED((d_DateTable[Year_WeekNum]))
      VAR ItStore = f_DailyPlan[Store]
      Return
      CALCULATE ( SUM ( 'f_DailyFootfall'[Traffic]),
      ALL ( 'f_DailyFootfall' ),
      ALL ( d_DateTable ),
      f_DailyFootfall[Store]= ItStore,
      d_DateTable[Year_WeekNum] = LY_SKWeek
      )
      )
      VAR f_WeeklyFigures_Enriched_Filtered =
      Filter(f_WeeklyFigures_Enriched, [Include] = 1)
      VAR Transactions =
      SUMX (
      f_WeeklyFigures_Enriched_Filtered,
      if([value]<>0 && [Footfall]<>0,[value])
      )
      VAR Footfall =
      SUMX (
      f_WeeklyFigures_Enriched_Filtered,
      if([value]<>0 && [Footfall]<>0,[Footfall])
      )
      VAR Result = DIVIDE(Transactions,Footfall)
      RETURN
      IF ( Result = 0, [ND_Sign], Result )

       

       

      As you can see I have to use the addcolumn for the 1st table "DailyPlan" and i have tried to use return to bring the 2nd table but didn't work.

       

      Any other way or idea to formulate this formula?

       

      Thanks you so much for your help!!

      • v-xiaotang's avatar
        v-xiaotang
        Community Support
        5 years ago

        thanks for your reply alexcatala 

        because still not very clear about your measure and model, so I'll give you one quick example, see

        there're 3 tables in my model, I create relationships between them, then I combine the columns of table 2 and table 3 with table 1.

         

        Table = ADDCOLUMNS(Table1,"c1",CALCULATE(MIN(Table2[Column2])),"c2",CALCULATE(MIN(Table3[Column2])))

         

         

        Best Regards,

        Community Support Team _ Tang

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