Comparison to different baseline values based on dimensions

[ScottS28]

I have a dataset that represents a collection of stores and their sales performance. The stores are geographically arranged into districts. We expect that districts will correlate to sales performance; districts containing large cities will have more sales than districts with no cities.

However the stores also have varying merchandising types. We expect that merchandising type will also correlate to sales performance; a store with [Merchandising Type] = 'Modern' should have higher sales than [Merchandising Type] = 'Legacy'

We want to see how a store's performance compares to average store performance within the district but also the average performance as compared to other similar Merchandising Types within the district.

We have the following data:

District	Store	Merchandising Type	Sales Revenue
A	1	Modern	5000
A	2	Modern	3000
A	3	Legacy	1000
A	4	Modern	1000
B	1	Legacy	2000
B	2	Legacy	3000
B	3	Modern	5000
C	1	Modern	500
C	2	Legacy	1500
C	3	Modern	1000
C	4	Legacy	1000

What I would like to display is the % above average revenue for each record within its district as well as within its district and merchandising type. In other words, I want to display the following 4 additional fields:

District	Store	Merchandising Type	Sales Revenue	District Average	District Merchandising Average	% Above District Average	% Above District Merchandising Average
A	1	Modern	5000	2500	3000	100%	67%
A	2	Modern	3000	2500	3000	20%	0%
A	3	Legacy	1000	2500	1000	-60%	0%
A	4	Modern	1000	2500	3000	-60%	-67%
B	1	Legacy	2000	3333.33	2500	-40%	-20%
B	2	Legacy	3000	3333.33	2500	-9%	20%
B	3	Modern	5000	3333.33	5000	50%	0%
C	1	Modern	500	1000	750	-50%	-33%
C	2	Legacy	1500	1000	1250	50%	20%
C	3	Modern	1000	1000	750	0%	33%
C	4	Legacy	1000	1000	1250	0%	-20%

I would like the values of these fields to be fixed regardless of selections, so that if I am looking only at the record representing Store A3 (District A store 3), I can see the comparison to baseline values within the district and merchandising type. Otherwise I will always only see that Store A3 is performing 0% above both averages (since the datasets with selections applied do not include any other stores or merchandising types).

What are the expressions I need to use?

[MayilVahanan]

Hi @ScottS28 

Try like below

Exp 1: Sum([Sales Revenue])

Exp2: Avg(TOTAL <District> [Sales Revenue])

Exp3: Avg(TOTAL<[Merchandising Type], District> [Sales Revenue])

Exp4: (Column(1)-Column(2))/Column(2)

Exp5: (Column(1)-Column(3))/Column(3)

If you want to ignore the store & merchandise selection, include the set analysis for first 3 exp like below

1.Sum({<Store=,[Merchandising Type]=>}[Sales Revenue])

2.Avg({<Store=,[Merchandising Type]=>}TOTAL <District> [Sales Revenue])

3.Avg({<Store=,[Merchandising Type]=>}TOTAL<[Merchandising Type], District> [Sales Revenue])

hope it helps and give ideas for your requirement