Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
21 | |
7 | |
6 | |
5 | |
5 |
User | Count |
---|---|
27 | |
10 | |
10 | |
9 | |
6 |