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 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:
And here is the Power BI version, where I'm stuck:
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.
Solved! Go to Solution.
@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:
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 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:
Let me know if this helps, or if this still doesn't make sense.
Thanks again.
@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:
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
25 | |
10 | |
7 | |
6 | |
6 |
User | Count |
---|---|
30 | |
11 | |
11 | |
10 | |
6 |