DAX

[kalpesh07]

Hi All,
I need help with DAX Formula to calculate Market Share.

I've Master Data which is very clean but is a combination of Value & Volume by Segment,Manufacturer, Brand at MONTH Level.

- Basically, I want to be able the DAX formula to understand that Share of Market is the SUM of ONE Brand volumes of a given Month/Qtr/Year DATA POINT, vs. the total SEGMENT of that very Month/Quarter/Year. Which would then help me look at it/slice and dice by Brand/Mfg etc ...

=DIVIDE(CALCULATE(sum(MarketData[Sales Value])),CALCULATE(SUM(MarketData[Sales Value]),FILTER(ALLSELECTED(MarketData),MarketData[SEGMENT]=MAX(MarketData[SEGMENT]))),0)

I've created a measure using this formula! but Problem I'm facing is - It is dividing the Value/Volume of particular Month/Year with TOTAL Value/Volume of Segment (It is not filtering the Calendar). I want Share of Market is the SUM of Brand volumes of a given Month/Qtr/Year DATA POINT, vs. the total SEGMENT of that very Month/Quarter/Year. 

Segment	Market	Brand	Month	Sales
Adult	1	A	1/2/2024	233
Kids	1	B	1/2/2024	323
Kids	2	C	1/3/2024	17.368

[Anonymous · Accepted solution]

HI @kalpesh07,

Perhaps you can try to use the following measure formula if it suitable for your requirement:

formula =
DIVIDE (
    SUM ( MarketData[Sales Value] ),
    CALCULATE (
        SUM ( MarketData[Sales Value] ),
        ALLSELECTED ( MarketData ),
        VALUES ( MarketData[SEGMENT] ),
        VALUES ( 'Calendar'[Date] )
    ),
    0
)

Regards,

Xiaoxin Sheng

[kalpesh07]

Hi @Anonymous Can you help me with the same Measure but calculating the same as rolling period. I need help with Calculating on Rolling period Basis. MAT means last 12 Month.
So I've to calculate Sales% basis Last 12 month for every month like

Jan'24 MAT (Feb23-Jan24)
Feb'24 MAT (Mar23-Feb'24)
Mar'24 MAT (Apr23-Mar24)

[NILU090]

i have a question, why did you use following statement, it may have filtered from Filter from Page.

VALUES ( MarketData[SEGMENT] ),
VALUES ( 'Calendar'[Date] )

[kalpesh07]

@Anonymous Thanks!

[kalpesh07]

Hi @rajendraongole1 
No this is not working! I've a seperate calendar table. I've used related function.

=

DIVIDE(

SUM(MarketData[Sales Value]),

CALCULATE(

SUM(MarketData[Sales Value]),

FILTER(

ALLSELECTED(MarketData),

MarketData[SEGMENT] = MAX(MarketData[SEGMENT]) &&RELATED('Calendar'[Date])

= MAX('Calendar'[Date])

)

),

0

)

[rajendraongole1]

Hi @kalpesh07 - Can you try below calculation, adding with segment and dateperiod . Hope you already have the date table in your model.

Market Share =
DIVIDE(
SUM(MarketData[Sales]),
CALCULATE(
SUM(MarketData[Sales]),
FILTER(
ALLSELECTED(MarketData),
MarketData[SEGMENT] = MAX(MarketData[SEGMENT]) &&
'DateTable'[DatePeriod] = MAX('DateTable'[DatePeriod])
)
),
0
)

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!