Market share for shop and category

[Dobrodej]

I am trying to calculated market share for below table but have some problems to calculate it properly...

Could you please help me to create correct calculated measures for this table.

for example category Cycling Backpacks  includes 3 manufacturer,  shop tisimo has only 2, so now brand overview table is missing 1 brand and shows incorrect results.

my measures 

__all_selected_Clicks_shop =CALCULATE(sum('input'[click_count]))

__clicks_brand =CALCULATE(sum('input'[click_count]), ALLEXCEPT('input','input'[manufacturer_name],'input'[category_name_lvl_7],input[click_count]))

__clicks_total_brand = CALCULATE(sum(input[click_count]),REMOVEFILTERS(),VALUES(input[category_name_lvl_7]))

__market_share =

var _totalCategories=CALCULATE(sum(input[click_count]),REMOVEFILTERS(),VALUES(input[category_name_lvl_7]))

var _brandTotal =CALCULATE(sum(input[click_count]),REMOVEFILTERS(),VALUES(input[manufacturer_name]))

RETURN (_brandTotal/_totalCategories)

__my_share =

var _totalCategories=CALCULATE(sum(input[click_count]),REMOVEFILTERS(),VALUES(input[category_name_lvl_7]))

var _shopTotal =CALCULATE(sum(input[click_count]),ALLSELECTED(input[shop_name]))

RETURN (_shopTotal/_totalCategories)

expected result is

expected result should also react to click date filter and avg price filter.

Thanks

[Dobrodej]

hi, if i filter only one filter  cycling backpack or shop_name, it shows data properly . but if i keep both of them it shows only combintion of these two filters ( something like inner join 😉 )

[Anonymous]

Hi @Dobrodej ,

Trying to change the relationship between these two tables, the flow of relationship from child class to parent class is changed to unidirectional.

Best regards,
Community Support Team_ Scott Chang

[Dobrodej]

Hi, the main problem is that currently my measures are incorrect, and i cant create a table with expected result.

click_date	click_count	product_id	manufacturer_name	average_price	category_name_lvl_7	shop_name
29/04/2024	1	1035690952	HOT	411	Cycling dresses	kolo
29/04/2024	5	1035690952	HOT	411	Cycling dresses	kolo
29/07/2024	1	1264358671		502	Cycling backpacks	osport
01/07/2024	1	1273318287	Magoon	474	Cycling backpacks	osport
15/07/2024	1	1269033766	Magoon	488	Cycling backpacks	web1ue
03/06/2024	1	1221786366	Magoon	446	Cycling gloves	osport
13/05/2024	1	1022001307	Magoon	425	Cycling dresses	osport
05/02/2024	1	1142597990	HOT	327	Cycling dresses	kolo
01/07/2024	1	1120514931	HOT	474	Cycling backpacks	kolo
20/05/2024	1	755696670	HOT	432	Cycling backpacks	web1ue

so if i will use 2 slicers
category  cycling backpacks 
 shop  osport 
the result should be 

manufacturer	all	share all	shop	share shop
HOT	2	40%	1	20%
Magoon	2	40%		0%
(blank)	1	20%	1	20%

thanks

[Dobrodej]

unfortunately the sysetem doesnt allow me to send power bi file.
currently modele shows only 2 brand. 
for example category  cycling backpacks contains 3 manufacturer
but shop  osport sells only 2 of them 
the result with these two filters should be like this

manufacturer	all	share all	shop	share shop
HOT	2	40%	1	20%
Magoon	2	40%		0%
(blank)	1	20%	1	20%

the result with shop filter should be 

slicer: shop
Row Labels	all	share all	shop	share shop
HOT	8	62%		0%
Magoon	4	31%	3	23%
(blank)	1	8%	1	8%
Grand Total	13
the result with category filter
slicer:  category
Row Labels	all	share all	shop (all shops)	share shop
HOT	2	40%	2	40%
Magoon	2	40%	2	40%
(blank)	1	20%	1	20%
Grand Total	5		5

thanks

[Dobrodej]

click_date	click_count	product_id	manufacturer_name	average_price	category_name_lvl_7	shop_name
29/04/2024	1	1035690952	HOT	411	Cycling dresses	kolo
29/04/2024	5	1035690952	HOT	411	Cycling dresses	kolo
29/07/2024	1	1264358671		502	Cycling backpacks	osport
01/07/2024	1	1273318287	Magoon	474	Cycling backpacks	osport
15/07/2024	1	1269033766	Magoon	488	Cycling backpacks	web1ue
03/06/2024	1	1221786366	Magoon	446	Cycling gloves	osport
13/05/2024	1	1022001307	Magoon	425	Cycling dresses	osport
05/02/2024	1	1142597990	HOT	327	Cycling dresses	kolo
01/07/2024	1	1120514931	HOT	474	Cycling backpacks	kolo
20/05/2024	1	755696670	HOT	432	Cycling backpacks	web1ue

[Ashish_Mathur]

Hi,

Baed on this 1 table that you have shared, show the expected result clearly.

[Dobrodej]

table 

click_date	click_count	product_id	manufacturer_name	average_price	category_name_lvl_7	shop_name
29/04/2024	1	1035690952	HOT	411	Cycling dresses	kolo
29/04/2024	5	1035690952	HOT	411	Cycling dresses	kolo
29/07/2024	1	1264358671		502	Cycling backpacks	osport
01/07/2024	1	1273318287	Magoon	474	Cycling backpacks	osport
15/07/2024	1	1269033766	Magoon	488	Cycling backpacks	web1ue
03/06/2024	1	1221786366	Magoon	446	Cycling gloves	osport
13/05/2024	1	1022001307	Magoon	425	Cycling dresses	osport
05/02/2024	1	1142597990	HOT	327	Cycling dresses	kolo
01/07/2024	1	1120514931	HOT	474	Cycling backpacks	kolo
20/05/2024	1	755696670	HOT	432	Cycling backpacks	web1ue

so if i will use 2 slicers
category  cycling backpacks 
 shop  osport 
the result should be 

manufacturer	all	share all	shop	share shop
HOT	2	40%	1	20%
Magoon	2	40%		0%
(blank)	1	20%	1	20%

thanks

[Dobrodej]

hi,
so based on this table the result should be 

manufacturer	all	share all	shop	share shop
HOT	2	40%	1	20%
Magoon	2	40%		0%
(blank)	1	20%	1	20%

thanks for your help!

[Ashish_Mathur]

Just cannot understand the expected result at all.

[Dobrodej]

tables witout filters lookd like this 

Thanks

[Anonymous]

Hi @Dobrodej ,

Do you keep only the [manufacturer_name] field and filter Cycling Backpacks, does it return the expected three brands?If so, please provide sample data.

Best regards,
Community Support Team_ Scott Chang