MCBMarketing Tracker

Re: Creating dynamic measure to calculate market penetration

MCBMarketing — Fri, 07 May 2021 11:56:31 GMT

This did it, thanks a lot!

I was struggling in the measure for the market penetration itself, not thinking about adjusting the market volume measure.

Creating dynamic measure to calculate market penetration

MCBMarketing — Fri, 07 May 2021 08:45:35 GMT

Hi,

I am looking for help with creating a measure for calculating the market penetration.

I have the data below, which shows the actual volume per country, per sales organization, per year, per material, per material shape. The Market volume, however, is the total market volume for the country. The formula for Market penetration = DIVIDE( [Actual volume], [Market volume], "" ).

Country	Sales Organization	Year	Material	Material shape	Actual volume	Market volume	Market penetration
Netherlands	A	2020	Plastic	Bars	2.400236104	40.4	5.9%
Netherlands	A	2020	Plastic	Sheets	0.926026057	102.3	0.9%
Netherlands	B	2020	Plastic	Bars	3.979754703	40.4	9.9%
Netherlands	B	2020	Plastic	Sheets	39.27918723	102.3	38.4%
Netherlands	C	2020	Plastic	Bars	0.269531685	40.4	0.7%
Netherlands	C	2020	Plastic	Sheets	0.601579538	102.3	0.6%
Netherlands	A	2020	Wood	Bars	4.428251924	268.1	1.7%
Netherlands	A	2020	Wood	Sheets	2.689547184	813.5	0.3%
Netherlands	B	2020	Wood	Bars	21.11403556	268.1	7.9%
Netherlands	B	2020	Wood	Sheets	190.302465	813.5	23.4%
Netherlands	C	2020	Wood	Bars	0.332333771	268.1	0.1%
Netherlands	C	2020	Wood	Sheets	0.602871471	813.5	0.1%

Now this is the result I expect for market penetration, since this is the deepest drill-down I can do with this data, but if I plot this on a higher level without the sales organization included, the market share sums up by the amount of the sales organizations within the country (by 3 in this case). Thus, my actual volume gets divided by 3 times the market volume, instead of only once.

Country	Year	Material	Material shape	Actual volume	Market volume	Market penetration	Expected value
Netherlands	2020	Plastic	Bars	6.649522492	121.2	5.50%	16.50%
Netherlands	2020	Plastic	Sheets	40.80679283	306.9	13.30%	39.90%
Netherlands	2020	Wood	Bars	25.87462125	804.3	3.20%	9.70%
Netherlands	2020	Wood	Sheets	193.5948837	2440.5	7.90%	23.80%

In this table the Market volume shows triple the value it should, since there are 3 sales organizations within this country.

Can someone help me to create a measure that takes all conditions into account?

Thanks a lot!

amitchandak Jihwan_Kim

Re: Calculating percentage of a subcategory related to total sum of another table

MCBMarketing — Thu, 15 Apr 2021 11:26:05 GMT

HarishKM Thanks for your help!

Will this also work if you create a line chart with the following?;

Axis = Year

Legend = Sales Organization

Value = %

I am afraid that the market volume for the country will be counted double in this case.

Could you share the PBI Desktop file with me to see how you did the bridge table?

Thanks a lot for the effort!

Re: Calculating percentage of a subcategory related to total sum of another table

MCBMarketing — Thu, 15 Apr 2021 10:30:11 GMT

amitchandak Thanks for your fast reply!

I have added the bridge tables, but the problem remains for the Sales Organization in Table1, that does not occur in Table2. What I am trying to accomplish is to get a line chart, which shows the market share over the years for each sales organization within the country (so the actual volume for each sales organization in Table1 needs to be divided by the total market share of the country in Table2).

Calculating percentage of a subcategory related to total sum of another table

MCBMarketing — Thu, 15 Apr 2021 08:34:28 GMT

Hi,

I am looking for help with an issue that I have been trying to solve, but cannot figure it out.

I want to calculate the market share, based on the actual volume divided by the market volume. I have two tables; one with the actual volume per sales organization within the same country and one with the market volume of the country. I would like to calculate the market share per sales organization. The data I have looks like this:

Table one with actual volume:

Year	Country	Sales Organization	Material	Material shape	Actual volume
2019	Netherlands	A	Wood	Planks	200
2020	Netherlands	A	Wood	Planks	190
2019	Netherlands	B	Wood	Planks	20
2020	Netherlands	B	Wood	Planks	18

Table 2 with market volume:

Year	Country	Material	Material shape	Market volume
2019	Netherlands	Wood	Planks	950
2020	Netherlands	Wood	Planks	800

I would like to be able to filter on Year, Country, Sales Organization, Material and Material shape.

The measure I have tried looks like this, but then it does not take the Sales Organization into account:

Market share = DIVIDE(

SUM ( 'Table1'[Actual volume] ),

SUM ( 'Table2'[Market volume] ),

)

Could someone help me out with this?

Thanks a lot!