Row level Division based on a % share of two columns

[jconte12]

Hello,

I am trying to solve a problem in power bi that i was able to solve in Excel fairly simply. I have 3 columns in my data --

1) last 12 months sales

2) Total Market Sales

3) Item Category

I created the following measure to evaluate my revenue share in the overal market: 

PY TTS Share = (SUM(NRG_DP_JOIN_3_BB[PY_TTS_REVENUE])/SUM(NRG_DP_JOIN_3_BB[PY_TTL_MKT_REVENUE])), which gets me a market share of ~36%. I've created a breakdown of my market share by each Item Category

PY Category Sales = SUM(NRG_DP_JOIN_3_BB[PY_TTS_REVENUE])

What I want to do is take the market share of each of my categories and subtract it from the ~36% to see if there is any opportunity for growth in a specific category, EG. Sneakers category market share was 12%. if I were to subtract 12% from 36% I would have 24% share opportunity.

Let me know if you need more clarification. I've been spinning my wheels unfortunately 😕

[jconte12]

Here is a sample of the data. So I want to be able to see what our Total market share is (12% in image). I would like to keep this as a static measure. Then I would like create a new measure that uses the static measure and if my company's market share for a particular category is less than my companies total market share (12%), I would subtract 12% from the market share in that category. 

[Anonymous]

Assume you have the following table named "MS".

Write the following measures:

PYR = SUMX(MS,MS[TTS PY Revenue])

MR = SUMX(MS,MS[TTL Market Revenue])

Market Share = DIVIDE([PYR],[MR],0)

Opportunity = 
VAR CategoryShare = [Market Share]
VAR TotalMarketShare = CALCULATE([Market Share],ALL(MS[Category]))
VAR Result = MAX(TotalMarketShare-CategoryShare,0)
RETURN if(Result>0,Result,BLANK())

This will give the following result.

[Anonymous]

This is fairly simple. But need to know your actual table structure and field names. Please post the table structure, field names, and some sample data here.