Forum Discussion

azale2407's avatar
azale2407
Frequent Visitor
3 years ago
Solved

DAX Measure

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!

Need Help

9 Replies

  • selimovd's avatar
    selimovd
    Most Valuable Professional
    3 years ago

    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

  • azale2407's avatar
    azale2407
    Frequent Visitor
    3 years ago

    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's avatar
      mangaus1111
      Solution Sage
      3 years ago

      Hi azale2407 ,

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

      • azale2407's avatar
        azale2407
        Frequent Visitor
        3 years ago

        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.

    • mangaus1111's avatar
      mangaus1111
      Solution Sage
      3 years ago

      Hi azale2407 ,

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

    • azale2407's avatar
      azale2407
      Frequent Visitor
      3 years ago

      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?