Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Calculate Market Share in Matrix

Hi, I have a matrix report that looks like the below.   I am trying to calculate the Market Share numbers here. So in my BI I have 1 field for sales values and the field column of Market Set is w...
Need Help
Anonymous's avatar
Anonymous
Not applicable
5 years ago

v-cazheng-msft 

 

Do you mean instead of the HASONEFILTER option?

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

Anonymous 

There is a link over ISFILTERED function, you can read it and check whether it applies to your situation. And then you can try to replace HASONEFILTER with ISFILTERED.

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi v-cazheng-msft 

     

    I am new to Power BI and I am not sure how to substitute this information into the great help you gave me initially. Can you help me more?

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

      Hi Anonymous 

      Can you show me your dax formula and some sample data without sensitive data for these columns you want to add into formula?

       

      Best Regards

      Caiyun Zheng

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi v-cazheng-msft 

         

        Here is the current DAX that you provided me and this works based on what I originally told you but I failed to let you know I had other slicer filters that can apply:

         

        Market share =
        VAR total_item =
        CALCULATE ( SUM ( 'Assortment Data' [Sales] ), ALLEXCEPT ( 'Assortment Data', 'Assortment Data' [Item] ) )
        VAR total_market_set =
        CALCULATE ( SUM ('Assortment Data' [Sales] ), ALLEXCEPT ( 'Assortment Data', 'Assortment Data' [Market Set] ) )
        RETURN
        IF (
        HASONEVALUE ( 'Assortment Data' [Item] ),
        IF (
        HASONEFILTER ( 'Assortment Data' [Market Set] ),
        SELECTEDVALUE ( 'Assortment Data' [Sales] ) / total_item,
        1
        ),
        total_market_set / total_item
        )

        Here is a snapshot of the view of what I am trying to accomplish. Notice I have 2 Market share calculations of them and the sum of the 2 in each row should equal 100% due to how I have the report laid out.
         
        Given the combined market share numbers are a lot smaller than 100% in most cases it leads me to believe it isn't taking into consideration the other slicer filters I have on the page.