DAX Measure

[azale2407]

I have a senario in Power BI that I can't seem to figure out.  I have a table with various loan data, i.e. interest rate, UPB, DTI, etc.  I also have a column with historical price these loans sold at in the past.  Obviously, no two loans are identical.  So, i have created Power BI to dynamically filter by various parameters in order to see what loans with similar characteristics fetched pricing-wise in the past, i.e. interest rate between 4 and 4.5%.  That is a simplified overview of my power bi model.  

My question is I am trying to create a measure that is as follows:

Yield = (Interest Rate of this specific loan * UPB of this specific loan) / (UPB of this specific loan * Average Historical pricing given selected parameters via slicers)

The problem is the interest rate and UPB need to be filtered by a specific loan # I select within one slicer.  And the Historical pricing needs to be an average of pricing filtered by multiple variables in other slicers, i.e. range of rate, range of DTI, etc.  But only filter by these if/when a particular slicer is selected.

I have scorecards created that can show me Interest Rate and UPB of the specific loan.... then I have another scorecard that calculates the average historical pricing with all other slicers filtering excluding Loan #.... I just can't figure out how to get the Yield formula/measure to have 2 variables filter off of the Loan # slicer selection and the 3rd variable of the equation filter off of the other different loan characteric slicers.  

Any direction or thoughts would be appreciated.

Thanks!

[mangaus1111 · Accepted solution]

Hi @azale2407 ,

see my new pbi file. I have created a new table and a new slicer for Loan Number that is not going to affect the calculation of the variable Z.

https://1drv.ms/u/s!Aj45jbu0mDVJi3BPJZwKA7Yqf21J?e=Je4U6u

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mangaus1111]

Hi @azale2407 ,

see my pbi file

https://1drv.ms/u/s!Aj45jbu0mDVJi3BPJZwKA7Yqf21J?e=P6iWwk

Is this your expected result? 

or you want to calculate first an average value for Loan number 21 in column [Price Sold]?

[azale2407]

Yes, the first table has my desired Yield correct.  Not sure I see how your power bi file answers how to do this though.

So basically, I ultimately want a scorecard to simily populate the Yield (in this case the correct result would be .04706)... but as I adjust the red slicers in my power bi.... the price of "85" would dynamically adjust because of the filters and ultimately change the yeild result.  Does that make sense?

[azale2407]

Here is an example....

• I need to create a measure to show Yield where:

(X * Y) / (X * Z) = Yield

• Z is dynamically changing as the three red bordered slicers are adjuested
• X and Y are exact values of the loan number selected in the blue slicer
• Loan 21 is new and has no price yet. So want to find yield where price is average of similar historical loan

•  

[mangaus1111]

Hi @azale2407 ,

please send the excel table through a One Drive LInk or copy-paste the table in text format

[mangaus1111]

Hi @azale2407 ,

how Z is calculated based on the 3 red slicers? Is Z the maximum of the slicer "LTV"?

[azale2407]

Here is a link to download both the excel file and the sample power bi file.  Let me know if you need anything else.

Excel - https://righthousecapital.sharefile.com/d-s5dfd3e35bf8248dcb6a961ebed752b8a

Power BI - https://righthousecapital.sharefile.com/d-sb72c40a14f2f4297a124f42aec1ec997

[azale2407]

No, Z is simply the average of Price.  There are 3 slicers simply filter what values are included.  i.e. LTV between 70-85, DTI between 35-45, and Rate between 4-4.5.... but obviously i need those ranges to be dynamic and easily chnaged.  3 slicers are my filters.... then after filtered... Z is simply average price.

[selimovd]

Hey @azale2407 ,

your scenario seems to be a little bit more complicated. I think with a description only it's hard to help you to get the result you need.

Can you create a demo file where you describe your scenario, then what the result should look like and where you're struggling?

I think that would be the best approach to help you.

Best regards

Denis