How to keep total base constant from the data table

[p_kaushal]

Hi I have a table with the below columns

Yearweeknumber (e.g. 201701)

Day text (Mon,Tues, Wed, Thurs)

Region

Store format

Channel

Competitor store

Product

Sales TY

Sales LY

I need to calculate a metric-Sales contribution by day.The formula in excel would be (Total sales TY-total sales LY)/Total Sales TY(for the respective day and week)

The denominator shouldn't change if I use other metrics such as product or region or channel.The numerator should change accordingly but the denominator i.e. the total sales for let's say Monday should remain the same irrespective of the change in categories we look at.

I would usually have a week no selected in the slicer when I look at contribution by day.

I have tried working this out and the below formula works where 'all store by day' is my table name.

Here I have put everything under the 'ALL' section other than Day_text and Week no,therefore it takes the correct base.

LFL contribution(by day) = (SUM('all store data by day'[LFL_Sales_SVTY])-SUM('all store data by day'[LFL_Sales_SVLY]))/CALCULATE(SUM('all store data by day'[LFL_Sales_SVTY]),ALL('all store data by day'[region],'all store data by day'[channel],'all store data by day'[competitor],'all store data by day'[store_affluence],'all store data by day'[store_format],'all store data by day'[psg]))

But,the issue arises when I try to get the same as I have a relationship between this table and another which is at 'PSG' level.The relationship between them is at psg level.If I take any other column from the other table to visualize my data my LFL contribution is incorrect as that column name is not in the ALL expression.

How do I make this ALL in the denominator include anything else from other tables too?

Thanks a lot!

[v-qiuyu-msft]

---

But,the issue arises when I try to get the same as I have a relationship between this table and another which is at 'PSG' level.The relationship between them is at psg level.If I take any other column from the other table to visualize my data my LFL contribution is incorrect as that column name is not in the ALL expression.

How do I make this ALL in the denominator include anything else from other tables too?

---

Hi @p_kaushal,

Can you share some sample data of 'all store data by day' table and the other table which has relationship use 'PSG'?

Best Regards,
Qiuyun Yu

[p_kaushal]

Hi @v-qiuyu-msft,

I have uploaded sample data here.  The first tab has the 'all store data by day ' I have mentioned and the second has 'PSG' level data.

When I use the above formula as mentioned in the first table it works fine.But when I take 'Junior Buyer' from the second table the %contribution numbers goes for a toss.The relationship between these two tables is on 'PSG'