Forum Discussion

GoncaloCare's avatar
GoncaloCare
Helper I
5 years ago
Solved

Calculate Marketshare - Help

Hi. I need some help building a table with marketshare values.

So, imagine I have this table:

 

Year-MonthBrandValue
Jan'21A45
Jan'21B35
Jan'21A76
Jan'21C465
Jan'21B4564
Feb'21B363
Feb'21B4646
Feb'21C564
Feb'21C6456
Feb'21A346
Mar'21C36345
Mar'21A363
Mar'21B6546
Mar'21C3456
Mar'21A353

 

And I want to calculate the marketshare of each brand in each month.

The end result needs to be something like this:

Year-MonthBrandValue
Jan'21A0.023336548
Jan'21B0.886981678
Jan'21C0.089681774
Feb'21A0.027959596
Feb'21B0.404767677
Feb'21C0.567272727
Mar'21A0.01521365
Mar'21B0.139090156
Mar'21C0.845696194

 

To get this values, I calculated on excel as you can see in this image here .  The final table is the red circled one. And I also putted the formulas I used. 

 

This is to have a table with the values to build a line chart. I tried to put the vields and use "Percentage of Grand Total" with my real values, and it was not working. 

 

Hope you can help me!

Need Help

11 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    GoncaloCare , Try a measure like

     

    divide(sum(Table[Value]), calculate(sum(Table[Value]), filter(allselected(Table),Table[Year-Month] = max(Table[Year-Month]))))

     

    or

     

    divide(sum(Table[Value]), calculate(sum(Table[Value]), removefilters(Table[Brand])))

    • GoncaloCare's avatar
      GoncaloCare
      Helper I
      5 years ago

      Hi! Thank you very much! I will try that solution, and If I need any assistance, I'll reply again. Thanks!

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    5 years ago

    Hi, GoncaloCare 

    Please check the below picture and the sample pbix file's link down below.

     

     

    Percentage by month =
    IF (
    ISFILTERED ( 'Calendar'[Year-Month] ),
    DIVIDE (
    SUM ( Data[Value] ),
    CALCULATE ( SUM ( Data[Value] ), ALLSELECTED ( Brands[Brand] ) )
    )
    )

     

     

    https://www.dropbox.com/s/czwzog2rny4zypo/goncalocarre.pbix?dl=0 

     

    Hi, My name is Jihwan Kim.

     

    If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

     

    Linkedin: https://www.linkedin.com/in/jihwankim1975/

    • GoncaloCare's avatar
      GoncaloCare
      Helper I
      5 years ago

      Hi! I will for sure try your solution! I'll reply again If I need any assistance! Thank you very much!

    • GoncaloCare's avatar
      GoncaloCare
      Helper I
      5 years ago

      Hi again Jihwan_Kim !

       

      So, it's working great! 

      I have a question. In the line chart, I have 188 Brands, but only like 30/50 are showing. Do you know why that is happening? is that a limitation?

       

      Thanks!

      • Jihwan_Kim's avatar
        Jihwan_Kim
        Super User
        5 years ago

        Hi, GoncaloCare 

        Thank you for your feedback.

        I am not sure about how is the limitation of showing how many categories (or legends) in a linechart.

        I also sometimes face a similar situation as what you just described.

        However, if I see over 20 lines in one line chart, I don't think it creates values or insights for readers. Or, perhaps you can try to select other visualizations, like a scatter chart for instance, if you need to show more than 20~30 categories.

        Thank you.