Look up alphanumeric values based on aggregated numeric values

[Anonymous]

Hi everyone,

Thank you for taking the time to read this post. I need help performing a look up to an alphanumeric value based on a weighted average calculation. Here is a specific example.

I’m trying to look up and return the alphanumeric bond rating based on the weighted average bond rating calculation (rounded to the nearest whole number). I was able to get the lookup working in Excel but was unable to get the lookup to work in Power BI.

Here are the steps I took and the problem I’m encountering.

This is the rating lookup table.

This is the calculation I performed in Excel. The weighted average rating for the bonds John Smith sold is 7.62. When rounded to the nearest whole number, the weighted average rating of 8 maps to the alphanumeric mapping MBaa1. Notice how Excel is able to perform the lookup correctly.

This is the calculation and lookup I performed in Power BI. Although the weighted average is correctly calculated and rounded, the lookup value returned is incorrect; see the subtotal row for John Smith. Rather than returning MBaa1 per the lookup table, Power BI is returning MA1.

This is a snapshot of my data model as well as the formula I’m using to perform the lookup.

Here's the formula in textual form for easy copying and pasting: 

Weighted_Rating_Alphanumeric = LOOKUPVALUE(ratingsLookup[Bond_Rating_Label],ratingsLookup[Bond_Rating],bondSales[Rounded_Weighted_BondRating])

How can I fix my Power BI model and calculation so that the correct alphanumeric bond rating is pulled in for the aggregate calculation?

Thank you for your help!

- Rofi

[d_gosbell · Accepted solution]

You've created the Weighted_Rating_Alphanumeric as a calc column on the bond, so it's calculated and stored per bond then the matrix is just pulling out the "first" value

If you want the lookup to be done dynamically at the subtotal level you would need to use a measure, not a column so that the lookup can be done based on the rounded rating at the "John Smith" level.

[Anonymous]

Thank you!