Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MCBMarketing
Regular Visitor

Creating dynamic measure to calculate market penetration

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], "" ).

 

CountrySales OrganizationYearMaterialMaterial shapeActual volumeMarket volumeMarket penetration
NetherlandsA2020PlasticBars2.40023610440.45.9%
NetherlandsA2020PlasticSheets0.926026057102.30.9%
NetherlandsB2020PlasticBars3.97975470340.49.9%
NetherlandsB2020PlasticSheets39.27918723102.338.4%
NetherlandsC2020PlasticBars0.26953168540.40.7%
NetherlandsC2020PlasticSheets0.601579538102.30.6%
NetherlandsA2020WoodBars4.428251924268.11.7%
NetherlandsA2020WoodSheets2.689547184813.50.3%
NetherlandsB2020WoodBars21.11403556268.17.9%
NetherlandsB2020WoodSheets190.302465813.523.4%
NetherlandsC2020WoodBars0.332333771268.10.1%
NetherlandsC2020WoodSheets0.602871471813.50.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.

 

CountryYearMaterialMaterial shapeActual volumeMarket volumeMarket penetrationExpected value
Netherlands2020PlasticBars6.649522492121.25.50%16.50%
Netherlands2020PlasticSheets40.80679283306.913.30%39.90%
Netherlands2020WoodBars25.87462125804.33.20%9.70%
Netherlands2020WoodSheets193.59488372440.57.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 

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

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.

 

Picture3.png

 

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

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


Go to My LinkedIn Page


View solution in original post

3 REPLIES 3
Anonymous
Not applicable

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
Super User
Super User

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.

 

Picture3.png

 

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

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


Go to My LinkedIn Page


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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.