Forum Discussion

Julia_Mav's avatar
Julia_Mav
Icon for Helper II rankHelper II
2 years ago
Solved

Market Share 100%

Hi, could someone please help with the market share percentage formula? I would like it to match the one in the screenshot, and even if any filters are applied, I still want to see where 100% equals the total products. Thank you in advance!
'Product details'[Product Category] 'Sales'[Sales$] 'Procuct'[Product Name]
 

Need Help
Tips and Tricks
  • danextian's avatar
    danextian
    2 years ago

    Hi Julia_Mav ,
    I was supposed to post this earlier but forgot to hit reply...

     

    Is Product a separate dimensions table? If so:

    • what is the relationship flow of this table to the fact ta ble? - should be one to many and single direction
    • which column is in the visual/slicer? From fact or dimension?

    You can see in the below screenshot that your formula should work fine assuming your the correct setup is used.

     

    Please see attached pbix for you reference.

4 Replies

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    2 years ago

    Hi Julia_Mav 

    What is this for?

    Please provide context?

    By 100% you mean, you want the total to be always 100% even if the sum of the breakdown isn't? Please  elaborate. Provide a workable sample data that isn't an image and your expected result from that sample data.

    • Julia_Mav's avatar
      Julia_Mav
      Icon for Helper II rankHelper II
      2 years ago

      Thank you danextian. 

      I am using this formula, and it works when no filters are applied. However, when I filter for a specific product category, I see 100% instead of seeing the proportion it represents of all products.  
       
      Formula: 
      % = 
      var _totalsalesallproducts =
      CALCULATE([Sales $], ALL(‘Product'[Product Name]))
      RETURN DIVIDE(
          [Sales $],
          _totalsalesallproducts)


       
       
      • danextian's avatar
        danextian
        Icon for Super User rankSuper User
        2 years ago

        Hi Julia_Mav ,
        I was supposed to post this earlier but forgot to hit reply...

         

        Is Product a separate dimensions table? If so:

        • what is the relationship flow of this table to the fact ta ble? - should be one to many and single direction
        • which column is in the visual/slicer? From fact or dimension?

        You can see in the below screenshot that your formula should work fine assuming your the correct setup is used.

         

        Please see attached pbix for you reference.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Julia_Mav ,

     

    Thanks for the reply from danextian , please allow me to provide another insight: 

     

    Try the following expression:

    Market Share % = 
VAR _CurrentCategory = SELECTEDVALUE('Product details'[Product Category])
VAR _TotalSalesAllProductsInCategory =
    CALCULATE(
        [Sales $],
        ALL('Product'),
        'Product details'[Product Category] = _CurrentCategory
    )
RETURN
    DIVIDE(
        [Sales $],
        _TotalSalesAllProductsInCategory
    )


    Alternatively, you can try using calculated columns instead of measures, which are static values added to a table and calculated row by row as the data is loaded. In contrast, a measure is a dynamic value that changes depending on what you are looking at in the report, calculated dynamically in the context of the visual object's filters. More detailed information can be found at the link: 

    Understanding Measures vs Calculated Columns in Power BI (gorilla.bi)

     

    If these still didn't solve your problem. Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

     

    Best Regards,

    Clara Gong

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