Forum Discussion

kalpesh07's avatar
kalpesh07
Frequent Visitor
2 years ago
Solved

DAX

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. 


SegmentMarketBrandMonthSales
Adult1A1/2/2024233
Kids1B1/2/2024323
Kids2C1/3/202417.368




 

Show and Tell
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      kalpesh07
      Frequent Visitor
      2 years ago

      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's avatar
        NILU090
        Helper II
        1 year ago

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

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

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    2 years ago

    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!!

  • kalpesh07's avatar
    kalpesh07
    Frequent Visitor
    2 years ago

    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

    )