Forum Discussion

alexcatala's avatar
alexcatala
Icon for Helper IV rankHelper 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 a...
Need Help
v-xiaotang's avatar
v-xiaotang
Icon for Community Support rankCommunity 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
    Icon for Helper IV rankHelper 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
      Icon for Community Support rankCommunity 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.

      • alexcatala's avatar
        alexcatala
        Icon for Helper IV rankHelper IV
        5 years ago

        v-xiaotang 

         

        Hi, so sorry if the subject is unclear. 

         

        I do not need a new table, I just need the formula to obtain the LFL dividing "Footfall" and "Transactions". The question was, in the example of the formula attached, "Footfall" and "Transaction" came from the same table, so the creator just used Addcolumns as there is only 1 table needed.

         

        Now, my problem comes, "Footfall" and " Transaction" are coming from a different table.

         

        So, my question is: Is there a way to use Addcolumns from 2 different tables? Or what the best way to create this formula.

         

        I have tried this:

         

        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 )
         
        But like this the value is inaccurate. 
         
        My 1st thought was to rewrite the 1st part, where the addcolumns is added, as it is making really complicated to formulate with the other table.  As I can read,  Return with the other part of the table, but after trying to add the other table " footfall" where it calculates the value from the footfall, the value I am obtaining is inacurrate.
         
        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 (Skechers)]))
        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 (Skechers)] = LY_SKWeek
        )
        )
         
        Any thought on how to rewrite the formula?
         
         I hope it makes sense this time.