monthly market share for each product by account

[ThisIsMyRealNam]

Hi everyone,

I'm a complete newbie. I'm just playing with a dataset that I have living in excel. 

It is monthly sales on all accounts.

Packs are 5 different products

What I would like to create is a monthly market share for each product on each account. For the life of me i've done my head in trying to get this together in my spare time.

Many thanks for any help 

[TomMartens]

Hey,

welcome to the forum, and welcome to the mysteries and pleasures of Power BI

Before I'm able to help you with your dataset, I need a little more information

• what is the market
  all outlets or is each outlet a separate market?
• what is the product?

Regards

[ThisIsMyRealNam]

each outlet is its own market.

However all accounts total to the national market.

Each product is a pharmaceutical drug and is interchangeable.

So 1 outlet may buy 20 of product 2 and 40 of product 3 and 8 of product 4 

Ideally i would want to get to the point that in that example

Product 2 has a 29.4% 

Product 3 has 58.82%

Product 4 has 11.7% market share

at the outlet level.

Thanks so much for any help

[v-yulgu-msft · Accepted solution]

Hi @ThisIsMyRealNam,

Suppose the data table is named as 'Market Share'. Please create a measure referring to below formula:

Percentage =
CALCULATE (
    SUM ( 'Market Share'[Units] ),
    ALLEXCEPT ( 'Market Share', 'Market Share'[OutletID], 'Market Share'[Pack] )
)
    / CALCULATE (
        SUM ( 'Market Share'[Units] ),
        ALLEXCEPT ( 'Market Share', 'Market Share'[OutletID] )
    )

Insert a Matrix visual to display data.

Best regards,
Yuliana Gu