Market Share Equation

[magnificen7]

Hi,

I need help with a market share equation. I have one table that looks like the following. 

Company/ Premiums Written/ Market share

The market share equation looks like the following and is calculating correctly.
=sum([Premiums Written Amount state])/aggr(NODISTINCT sum({<[Entity Name] = >} [Premiums Written Amount state]), [Year])

I have another table that is the following.

State/ Premiums written/ Market Share

I'd like to be able to select a company, and the market share calculates each states market share for just that 1 company being 100% versus all companies being the 100%.

For example if Chubb is selected and their total premium is $100, and their premium in CA is $10, I'd like to see 10% versus maybe 1% if the entire industry premium is $1000.

Hopefully that makes sense. If it doesn't I can try to explain more clearly.

Thanks in advance.

[edwin · Accepted solution]

if you require the user to select a company then you can do a simple divide by the total:

sum( [Premiums Written Amount state])/sum(total  [Premiums Written Amount state])

obviously, it will be across ALL companies if no company is selected 

[magnificen7]

Hi,

Thanks for responding! I tried that as well and it's not giving me an accurate market share by state. Is it because I have other metrics selected as well? Like the year?

This is what I'm getting with that equation. (attached picture) I would expect the market share for California to show 10.8%.

Do I need to add something in the equation for the year and for the state?

[edwin]

its hard to say what is wrong when there is no visibility to what you are doing also your number (10.8%)does not match your picture as you are only showing part of the numbers.  i used the same expression and im getting the rt numbers:

[magnificen7]

Hi Edwin,

Thanks again for getting back to me.

I realized I had this as my equation

=sum([Premiums Written Amount state])/sum({1} TOTAL [Premiums Written Amount state])

Instead of what you posted. I pasted in what you had and it worked. Thanks for your help!

[edwin]

np