Need equivalent of FOR loop to iterate over multiple table columns

[Anonymous]

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:

tstData.pbix

Thanks in advance for any and all help!

[daXtreme]

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.

[Anonymous]

Thank you @daXtreme for the advice. I have updated my question with additional detail and a new subject.