Creating dynamic measure to calculate market penetration

[MCBMarketing]

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 

[Anonymous]

Hi any chance you could help me with this one?

Business area	REGS	Sales new used	penetration
A	200	New	(Count of new) ‘New’/REGS
B	300	New
C	453	New
A	200	Used
B	234	Used
C	754	New
A	768	New

@Jihwan_Kim 

[Jihwan_Kim · Accepted solution]

Hi, @MCBMarketing 

Please check the below picture and the sample pbix file's link down below.

All measures are in the sample pbix file.

Also, when you insert Sales Org. Column, it still shows the correct result.

https://www.dropbox.com/s/le76k9l6pp1lv9d/mcbmarketing.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[MCBMarketing]

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.