Market share by different Packs/subsegments

[Anonymous]

Hi Gurus

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

I have a example dataset like below 

Market	Brand	Pack	Value Sales	Time
X	A	Small	100	Q1'20
X	A	Large	20	Q1'20
X	A	X Large	10	Q1'20
X	B	Large	70	Q1'20
X	B	Small	20	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!

[v-yanjiang-msft]

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]

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

[amitchandak]

@Anonymous , You might have to create two measures and switch using isinscope

https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/

similar example

IsInScope - Switch Rank at different levels: https://youtu.be/kh0gezKICEM

ValShare pack size=
DIVIDE (
SUM ( MAINTABLE[Value SALES)] ),
CALCULATE ( SUM ( MAINTABLE[Value sales ] ), ALL ( MAINTABLE[Pack Size] ) )
) *100