LFL Conversion from 2 different tables

[alexcatala]

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]

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]

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

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]

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

[alexcatala]

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]

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]

@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

[alexcatala]

@amitchandak Do you mind looking at this issue, please?