Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I am working with multi-year vendor sales data, and I'd like to compute how vendor market share changes over time for a given mix of products. The logic goes like this:
1) For each vendor and year, compute their market share as:
Market Share = DIVIDE( [Revenue], CALCULATE([Revenue], ALL(Vendors[Vendor])), 0)
2) For each vendor and year, compute their year-over-year market share as:
Market Share YoY =
VAR currentYeartMS = [Market Share]
VAR priorYearMS = CALCULATE([Market Share], SAMEPERIODLASTYEAR('Date'[Date]))
RETURN
IF(priorYearMS = 0, 0, currentYeartMS - priorYearMS)
3) For each vendor, create a market share distance index. I don't know how to do this, but conceptually it is:
MS Index =
VAR overallIndex = 0
VAR vendorIndex = 0
// For each vendor over all years
vendorIndex = (1 / NumYears) * SQRT(SUM([Market Share YoY]^2))
overallIndex = overallIndex + vendorIndex
next(vendor)
RETURN
overallIndex
The index should be computed for each product. It is a scalar value based on the current filter context.
Below is a sample .pbix file. On the 'Revenue & Market Share' page are a matrix table with the values of Revenue, Market Share, and Market Share YoY by vendor and by year, along with slicers for product, region, and vendor. There is also a 'Product Table' page, which is where I would like to display the MS Index values for each row of the product matrix table.
Here's a link to my data .pbix sample file:
Thanks in advance for any and all help!
If nobody answers your question for a long time, it most likely means people have a hard time understanding what you really want. Please try to rephrase your question in simpler terms or have a look at this.
Thank you @daXtreme for the advice. I have updated my question with additional detail and a new subject.
User | Count |
---|---|
54 | |
22 | |
19 | |
16 | |
11 |
User | Count |
---|---|
82 | |
55 | |
40 | |
20 | |
12 |