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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

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.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

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.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

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
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.