Forum Discussion

likmalik's avatar
likmalik
Icon for Helper I rankHelper I
5 years ago
Solved

Calculate Marketshare over 12months.

Hello,   I am trying to display the marketshare for various models over time and am struggling with which DAX formula to use. I have a simple table (country, brand, model, units sold, date). I wou...
Need Help
  • v-janeyg-msft's avatar
    v-janeyg-msft
    5 years ago

    Hi, likmalik 

     

    You can modify the measure:Add [Date]=SELECTEDVALUE(Sheet1[Date])

    VAR total =
    SUMX (
        FILTER ( ALLSELECTED ( Sheet1 ), [Date] >= startdate && [Date] <= enddate&& [Date]=SELECTEDVALUE(Sheet1[Date]) ),
        [Sales numbers]
    )
VAR single =
    SUMX (
        FILTER (
            Sheet1,
            [Date] >= startdate
                && [Date] <= enddate
                && [Model] = SELECTEDVALUE ( Sheet1[Model] )
                &&[Date]=SELECTEDVALUE(Sheet1[Date])
        ),
        [Sales numbers]
    )

     

    Best Regards

    Janey Guo

     

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

v-janeyg-msft's avatar
v-janeyg-msft
Icon for Community Support rankCommunity Support
5 years ago

Hi, likmalik 

 

Because it's not clear which models you need, and the definition over 12 months, I made a sample file that can be compared the two at will. I create some parameters as date slicer and a measure to calculate the desired result.

Like this:

Measure =
VAR a =
    SELECTEDVALUE ( StartYear[StartYear] )
VAR b =
    SELECTEDVALUE ( StartMonth[StartMonth] )
VAR c =
    SELECTEDVALUE ( EndYear[EndYear] )
VAR d =
    SELECTEDVALUE ( EndMonth[EndMonth] )
VAR startdate =
    DATE ( a, b, 1 )
VAR enddate =
    DATE ( c, d, 1 )
VAR total =
    SUMX (
        FILTER ( ALLSELECTED ( Sheet1 ), [Date] >= startdate && [Date] <= enddate ),
        [Sales numbers]
    )
VAR single =
    SUMX (
        FILTER (
            Sheet1,
            [Date] >= startdate
                && [Date] <= enddate
                && [Model] = SELECTEDVALUE ( Sheet1[Model] )
        ),
        [Sales numbers]
    )
RETURN
    DIVIDE ( single, total )

Reference:Use what-if parameters to visualize variables - Power BI | Microsoft Docs

Here is my sample .pbix file.Hope it helps.

If it doesn’t solve your problem, please feel free to ask me.

 

Best Regards

Janey Guo

 

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

likmalik's avatar
likmalik
Icon for Helper I rankHelper I
5 years ago

Thankou a lot v-janeyg-msft but that doesn't solve the problem.

 

Models I selected are not competing against each others during the month. Total of MS should be 100% each month if I calculate it by hand and it's not.

 

And if for exemple I select only one brand it will show 100% each month because it's not competeing against other during the month.  I don't know if you get the logic. 

 

Thankyou

  • v-janeyg-msft's avatar
    v-janeyg-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi, likmalik 

     

    You can modify the measure:Add [Date]=SELECTEDVALUE(Sheet1[Date])

    VAR total =
    SUMX (
        FILTER ( ALLSELECTED ( Sheet1 ), [Date] >= startdate && [Date] <= enddate&& [Date]=SELECTEDVALUE(Sheet1[Date]) ),
        [Sales numbers]
    )
VAR single =
    SUMX (
        FILTER (
            Sheet1,
            [Date] >= startdate
                && [Date] <= enddate
                && [Model] = SELECTEDVALUE ( Sheet1[Model] )
                &&[Date]=SELECTEDVALUE(Sheet1[Date])
        ),
        [Sales numbers]
    )

     

    Best Regards

    Janey Guo

     

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

    • likmalik's avatar
      likmalik
      Icon for Helper I rankHelper I
      5 years ago

      Thankyou v-janeyg-msft is it possible to send me the pbix because I struggle to modify the measure ?

      • v-janeyg-msft's avatar
        v-janeyg-msft
        Icon for Community Support rankCommunity Support
        5 years ago

        Hi, likmalik 

         

        I shared the sample In the reply above,You can download and only modify two variables in my old measure.

         

        Best Regards

        Janey Guo