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
v-cazheng-msft's avatar
v-cazheng-msft
Icon for Community Support rankCommunity Support
5 years ago

Hi Anonymous 

You can create a Measure to get the result you want.

 

Market share =

VAR total_item =

    CALCULATE ( SUM ( UPCData[Sales] ), ALLEXCEPT ( UPCData, UPCData[Item] ) )

VAR total_market_set =

    CALCULATE ( SUM ( UPCData[Sales] ), ALLEXCEPT ( UPCData, UPCData[Market Set] ) )

RETURN

    IF (

        HASONEVALUE ( UPCData[Item] ),

        IF (

            HASONEFILTER ( UPCData[Market Set] ),

            SELECTEDVALUE ( UPCData[Sales] ) / total_item,

            1

        ),

        total_market_set / total_item

    )

 

The result looks like this:

 

 

For more details, you can refer the attached pbix.

 

Best Regards,

Caiyun Zheng

 

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    v-cazheng-msft 

     

    Thank you very much...this works perfectly for the purposes of what I had posted.

     

    One thing I did not show is that I have additional filters from the same UPCDATA table for Geography, Category, Brand, Package Shape and Size. When I tried to start adding them to the HASONEFILTER line I got an error. Any help on how to make this work on all the filters on the page?

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        v-cazheng-msft 

         

        Do you mean instead of the HASONEFILTER option?