Need equivalent of FOR loop to iterate over multiple table columns

Anonymous — Sun, 04 Sep 2022 17:50:35 GMT

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!

Re: Need equivalent of FOR loop to iterate over multiple table columns

daXtreme — Sun, 04 Sep 2022 15:55:32 GMT

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.

Re: Need equivalent of FOR loop to iterate over multiple table columns

Anonymous — Sun, 04 Sep 2022 22:42:59 GMT

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

topic Need equivalent of FOR loop to iterate over multiple table columns in DAX Commands and Tips

Need equivalent of FOR loop to iterate over multiple table columns

Re: Need equivalent of FOR loop to iterate over multiple table columns

Re: Need equivalent of FOR loop to iterate over multiple table columns