Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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!
Solved! Go to 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
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.
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 |
|
|
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
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.
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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
86 | |
76 | |
74 | |
56 | |
45 |
User | Count |
---|---|
117 | |
105 | |
77 | |
66 | |
64 |