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

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

 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!

1 ACCEPTED SOLUTION
Super User

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.

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.

Click here and Go to My LinkedIn Page

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

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.

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.

Click here and Go to My LinkedIn Page

Regular Visitor

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

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Join our Community Sticker Challenge

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Power BI Monthly Update - July 2024

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

#### Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors