topic Re: Working with values in matrix table on another page in DAX Commands and Tips

Working with values in matrix table on another page

Anonymous — Sat, 10 Sep 2022 06:57:15 GMT

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.

Re: Working with values in matrix table on another page

lbendlin — Sun, 11 Sep 2022 23:03:52 GMT

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?

Re: Working with values in matrix table on another page

Anonymous — Sun, 11 Sep 2022 23:31:09 GMT

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:

Market Share = DIVIDE( [Revenue], CALCULATE([Revenue], ALLSELECTED(dim_Vendors[Vendor])), 0)
Market Share YoY =
VAR currentYeartMS = [Market Share]
VAR priorYearMS = CALCULATE([Market Share], SAMEPERIODLASTYEAR('dim_Date'[Date]))
RETURN
IF(priorYearMS = 0, 0, currentYeartMS - priorYearMS)
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.
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.

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

Re: Working with values in matrix table on another page

lbendlin — Mon, 12 Sep 2022 00:24:31 GMT

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.

Re: Working with values in matrix table on another page

Anonymous — Mon, 12 Sep 2022 03:18:50 GMT

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!