Product shares in total per market

[Sebastian_Dec]

Hi, I have a problem, I want to calculate the sales value per store per product_range (sales shares).
Unfortunately, the current function counts shares per total sales, how to change it so that each store gives 100%?

My function:

Sum({<Product_range -={'Petrol station'} >} Sales)
/
Sum({<Product_range -={'Petrol station'} >} TOTAL Sales)

How I have:

How I want to have:

	Store_01	Store_02	Store_03
Total	100%	100%	100%
Bulk goods	43%	21%	86%
Fresh products	21%	29%	5%
Bazaar	11%	15%	3%
Services	21%	29%	5%
Bag	4%	6%	1%

Example data:

Product_range_Id	Product_range	Store	Sales
1	Bulk goods	Store_01	100
2	Fresh products	Store_01	50
3	Bazaar	Store_01	25
4	Services	Store_01	50
5	Petrol station	Store_01	100
6	Bag	Store_01	10
1	Bulk goods	Store_02	44
2	Fresh products	Store_02	60
3	Bazaar	Store_02	30
4	Services	Store_02	60
5	Petrol station	Store_02	120
6	Bag	Store_02	12
1	Bulk goods	Store_03	1231
2	Fresh products	Store_03	72
3	Bazaar	Store_03	36
4	Services	Store_03	72
5	Petrol station	Store_03	144
6	Bag	Store_03	15

[LRuCelver · Accepted solution]

{<Product_range -= {'Petrol station'}>}
Sum(Sales)
/
Sum(total <Store> Sales)

[Sebastian_Dec]

Wow, Thank You LRuCelver!

full code:

Sum({<Product_range -= {'Petrol station'}>} Sales)
/
Sum({<Product_range -= {'Petrol station'}>} total <Store> Sales)

[steeefan · Accepted solution]

Another possible solution using Aggr():

{<Product_range -={'Petrol station'} >} Sum(Sales) / Sum(Aggr(NODISTINCT Sum(Sales), Store))

[Sebastian_Dec]

Hi, @steeefan , thank You, yes, its second good solution 🙂

Sum({<Product_range -={'Petrol station'} >} Sales) 
/
Sum(Aggr(NODISTINCT Sum({<Product_range -={'Petrol station'} >} Sales), Store))