Calculate Market Share in Matrix

[Anonymous]

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 what gives me Account or Remaining Market.

My Table Name is UPCData.

I tried to paste in a picture of my BI but this would not let me do it.

Any help?

Market Set	Account	Account	Remaining Market	Remaining Market	Grand Total
Item	Sales	Market Share	Sales	Market Share	Sales
CHESTER'S CORN AND POTATO SNACK FLAMIN HOT FRIES BAG 5.25 OUNCE	$305,670	45.1%	$371,529	54.9%	$677,199
LAY'S POTATO CHIP CLASSIC FLAT BAG 2.625 OUNCE	$230,270	35.8%	$412,205	64.2%	$642,475
RUFFLES POTATO CHIP CHEDDAR AND SOUR CREAM RIDGED BAG 2.5 OUNCE	$205,428	36.6%	$355,310	63.4%	$560,738
CHESTER'S CORN AND POTATO SNACK FLAMIN HOT FRIES BAG 3.625 OUNCE	$270,156	51.2%	$257,097	48.8%	$527,253
SABRITAS RUFFLES POTATO CHIP CHEESE RIDGED BAG 2.5 OUNCE	$184,196	47.0%	$208,040	53.0%	$392,236
RUFFLES POTATO CHIP CHEDDAR AND SOUR CREAM RIDGED BAG 8.5 OUNCE	$99,096	26.0%	$281,742	74.0%	$380,838

[v-cazheng-msft]

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]

@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?

[v-cazheng-msft]

Hi @Anonymous 

You can try to use the ISFILTERED DAX Function.

Best Regards

Caiyun Zheng

[Anonymous]

@v-cazheng-msft 

Do you mean instead of the HASONEFILTER option?

[v-cazheng-msft]

@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]

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]

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]

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.

[Anonymous]

@v-cazheng-msft 

Any thoughts?

[v-cazheng-msft]

Hi @Anonymous 

Sorry for late reply. Please try this Measure.

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],UPCData[Item] ) )

RETURN

   total_market_set/total_item

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]

@v-cazheng-msft 

Thank you for the response. This did not give me the expected results and I am now thinking it is because I have slicers on the the page for things like Geography, Category, SubCategory, etc....

Do I need to incorporate all the slicers into the formula?