Forum Discussion
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 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 |
11 Replies
v-cazheng-msftCommunity Support
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.
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