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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Sept PBI Carousel

Power BI Monthly Update - September 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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