cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
rislam1metlife Frequent Visitor
Frequent Visitor

Look up alphanumeric values based on aggregated numeric values

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.

 

 

BondRatingTable.png

 

 

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.

 

 

WeightedAverage_Excel.png

 

 

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.

 

 

PowerBI_Calculation.png

 

 

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

 

 

PowerBI_DataModel.png

 

 

 

PowerBI_WeightedAverageFormula.png

 

 

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
d_gosbell Senior Member
Senior Member

Re: Look up alphanumeric values based on aggregated numeric values

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

2019-02 First Subtotal.png

 

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.

2 REPLIES 2
d_gosbell Senior Member
Senior Member

Re: Look up alphanumeric values based on aggregated numeric values

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

2019-02 First Subtotal.png

 

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.

rislam1metlife Frequent Visitor
Frequent Visitor

Re: Look up alphanumeric values based on aggregated numeric values

Thank you!