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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
d_gosbell
Super User
Super User

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.

View solution in original post

2 REPLIES 2
d_gosbell
Super User
Super User

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.

Anonymous
Not applicable

Thank you!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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