Forum Discussion

ndna74's avatar
ndna74
Frequent Visitor
9 years ago
Solved

What-if / measure

Hi all

 

I've created a measure (below) that calculates loan yield that can be adjusted with a what-if parameter (rate change)

 

Rate adjust = SUM('_Portfolio Internal'[Amount])* (sum('_Portfolio Internal'[Rate]) +'Rate change'[Rate change Value])

 

When I change the rate on the what-if slider, the yield per loan increase/decrease is correct but the overal increase/decrease for the portfolio is not. This is because when I put this measure into a card, the calucation mutiplies the sum of all amounts with the sum of all rates.

 

Does anyone know how I can rectify this?

 

thanks

Nick

Need Help
  • Sean's avatar
    Sean
    9 years ago

    To get the correct value in the Total Row (or when in a Card) use this Measure instead

     

    Rate adjust =
SUMX (
    '_Portfolio Internal',
    '_Portfolio Internal'[Amount]
        * ( '_Portfolio Internal'[Rate] + 'Rate change'[Rate change Value] )
)

    SUMX will perform this per row and then add those results - instead of first summing each column

     

    Hope this helps! :smileyhappy:

3 Replies

  • Sean's avatar
    Sean
    Icon for Community Champion rankCommunity Champion
    9 years ago

    To get the correct value in the Total Row (or when in a Card) use this Measure instead

     

    Rate adjust =
SUMX (
    '_Portfolio Internal',
    '_Portfolio Internal'[Amount]
        * ( '_Portfolio Internal'[Rate] + 'Rate change'[Rate change Value] )
)

    SUMX will perform this per row and then add those results - instead of first summing each column

     

    Hope this helps! :smileyhappy:

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    9 years ago

    Can't really get a sense of your data and such with this explanation but it sounds like you need to filter your Card visual or put some ALLEXCEPT type filters in your SUM statements via a CALCULATE. Hard to really tell what is going on from the information given.

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Hi ndna74,

    Have you resolved your issue? If you have, please mark the right answer or helpful reply as answer. If you have not, you'd better share your sample table or screenshot for further analysis.

    Best Regards,
    Angelia