Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Working with values in matrix table on another page

I have a two-step calculation that seems to require that I first calculate values in a matrix table and then summarize those values in a different table. It's easy enough in Excel, but I don't know if it's possible in Power BI.

I'm starting with sales data by year, product type, product, region, and vendor. My matrix/pivot table sums sales per vendor by product and year, which is presented as percent of total to give market share. This gives me a table of market share per vendor by product and year. The table can be filtered by product type and region. That's easy enough in both Excel and Power BI.

 

Next, I need to perform a calculation for each product that involves looking up the market share values in the market share table. This is the part I can't figure out in Power BI. In Excel, I just use an INDEX lookup for values in the first table. 

 

To get a better idea of what I'm talking about, here is the Excel version, which works:

Excel version 

And here is the Power BI version, where I'm stuck:

Power BI version 

 

I'm hoping there's some clever subquery DAX coding that can replicate what I'm doing in Excel without having to create the intermediate table. Unfortunately, the stack in my brain just isn't that deep.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

@lbendlin Thank you so much!
Your solution is almost perfect. The only issue is with the countrows(v) denominator. The intent is to divide by the number of vendors who sell a given product.  In my test file, each product is sold by only 2 out of the 3 available vendors. Using countrows(v) will always return 3. Also - a very minor point - I multiply the market share numbers by 100. 
I made the following tweaks to your Index formula, and it now matches the Excel file:

Index2 =
VAR msTable = ADDCOLUMNS(VALUES(dim_Vendors[Vendor]), "YoY", [Market Share YoY])
RETURN
   DIVIDE(SQRT(SUMX(msTable, ([YoY]*100)^2)),
      CALCULATE(DISTINCTCOUNT('data'[Vendor]),
      'data'[Vendor] <>
BLANK()),
      0
   )
 
Thanks again!

View solution in original post

4 REPLIES 4
lbendlin
Super User
Super User

 

 

I need to perform a calculation for each product that involves looking up the market share values in the market share table.

 

 

 

Can you describe the business rules for that calculation? It is not clear (to me)  from the Excel page what these rules should be.  You shouldn't need to use any intermediate tables or lookups - a standard measure should suffice.  Your "Market Share YoY" measure seems to look ok? 

 

lbendlin_1-1662937364772.png

 

Anonymous
Not applicable

@lbendlin Thank you for your reply. I've been trying to use implicit measures, but by desire is greater than my skills.
The business rules are essentially as follows:

  1. Market Share = DIVIDE( [Revenue]CALCULATE([Revenue], ALLSELECTED(dim_Vendors[Vendor])), 0)
  2. Market Share YoY =
    VAR currentYeartMS = [Market Share]
    VAR priorYearMS = CALCULATE([Market Share], SAMEPERIODLASTYEAR('dim_Date'[Date]))
    RETURN
    IF(priorYearMS = 00currentYeartMS - priorYearMS)
  3.  The index for each year-over-year period and product is the square root of the sum of the squares of the [Market Share YoY] measure for each vendor that sold the product in that year in the current filter context. That is,
    Index = sqrt(VendorA[Market Share YoY]^2 + VendorB[Market Share YoY]^2 + ...)/(number of vendors with non-zero market share)
    This is what the Excel LET() function is calculating:
    =LET(
       Year2018,
          INDEX(marketShares,,ROW(B1)*$C$1-2),
       Year2019,
          INDEX(marketShares,,ROW(B1)*$C$1-1),
       SQRT(SUM(((100*Year2019)-(100*Year2018))^2))/
          COUNTIF(INDEX(marketShares,,ROW(B1)*$C$1-1),"<>0")
    )
    Where $C$1 is the number of years spanned by the data (in this case, 3). The ROW(B1*numYears-n) calculation is used to select the target column in the INDEX() function. Kludgy, I know, but that's how I got Excel to do what I wanted.
  4. I then want to plot the index values over the time span of the data. For the sample data provided, the two data points for each product would be 2018-2019 and 2019-2020, as in the Excel graph.

testData graph.png

Let me know if this helps, or if this still doesn't make sense.
Thanks again.

This should do it

 

Index = 
var v = addcolumns(values(data[Vendor]),"Y",[Market Share YoY])
return divide(SQRT(sumx(v,[Y]*[Y])),countrows(v),0)

see attached.

 

Anonymous
Not applicable

@lbendlin Thank you so much!
Your solution is almost perfect. The only issue is with the countrows(v) denominator. The intent is to divide by the number of vendors who sell a given product.  In my test file, each product is sold by only 2 out of the 3 available vendors. Using countrows(v) will always return 3. Also - a very minor point - I multiply the market share numbers by 100. 
I made the following tweaks to your Index formula, and it now matches the Excel file:

Index2 =
VAR msTable = ADDCOLUMNS(VALUES(dim_Vendors[Vendor]), "YoY", [Market Share YoY])
RETURN
   DIVIDE(SQRT(SUMX(msTable, ([YoY]*100)^2)),
      CALCULATE(DISTINCTCOUNT('data'[Vendor]),
      'data'[Vendor] <>
BLANK()),
      0
   )
 
Thanks again!

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.