Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Market share by different Packs/subsegments

Hi Gurus

I am a POWER BI newbie.. and I am stuck with something rather dumb..... 

 

I have a example dataset like below 

 

MarketBrandPackValue SalesTime
XASmall100Q1'20
XALarge20Q1'20
XAX Large10Q1'20
XBLarge70

Q1'20

XBSmall20

Q1'20

 

I have a measure as below to calculate total brand market share and its working well 

 

ValShare Brand =
DIVIDE (
SUM ( MAINTABLE[Value SALES)] ),
CALCULATE ( SUM ( MAINTABLE[Value sales ] ), ALL ( MAINTABLE[BRAND] ) )
) *100
 
If I use the same formula and then on matrix I put Pack size as rows, the shares for each pack size show as a total of the total market pack size (e.g. Brand A Large out of Brand A + B Large pack size)
 
however, I want to show a chart that shows the shares by respective pack size for each brand based on TOTAL category sales, meaning Brand A Large share based on total sales of the market 
 
I am at a loss, and cant really figure it out...
 
please help!
Need Help

3 Replies

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Anonymous ,

    According to your description, you want the values of Pack column adds to the value of brand according to the weight of Value Sales.

    Here's my solution, modify your formula like this:

    ValShare Brand =
DIVIDE (
    SUM ( 'MAINTABLE'[Value SALES] ),
    CALCULATE ( SUM ( 'MAINTABLE'[Value SALES] ), ALL ( 'MAINTABLE' ) )
) * 100

    I attach my sample below for reference.

     

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hello! 

      thanks for the reply, I tried this but ended up getting 100% as a result for all brands 😞 

       

      Could it be because of my relationships of tables?

      please see below, I have relationships to Date table and a separate pack size table from the main table. Teh separate packsize table just helps me club several packs into custom labels