Forum Discussion

BobMcC's avatar
BobMcC
New Member
3 years ago
Solved

Measure Returning

Hello,

I've been trying to proof out a .pbix.  I'm having an issue with one of the measures.

The measure name is 

WA LTV Locked All = IF(SUM('dmLoanInfo'[LoanAmount]) > 0 ,SUMX('dmLoanInfo',[LTV]*[LoanAmount]/SUM('dmLoanInfo'[LoanAmount])) / 100, BLANK())
 
I created an SQL script that returns different values than the measure.  Additionally, I exported the drill down data and ran the calculatiions in a spreadsheet... getting the same values that SQL is returning.
The ONLY way, I could get the .pbix to return the same values I was getting from both Excel and SQL was to split the original measure into separate measures.
  • Create Measure:  LTV*LoanAmount = SUMX('dmLoanInfo',[LTV]*[LoanAmount])
  • Create Measure: SumOfLoanAmount = sum(dmLoanInfo[LoanAmount])
  • Create Measure: WA LTV MinLogic2 = [LTV*LoanAmount] / [SumOfLoanAmount]

Using the WA LTV MinLogic2 Measure I see the same values seen while proofing via Excel & SQL.

Trying to understand the difference in logic being applied - Original Measure VS New one using 2 new broken out measures

 

This link is to a bare bones version of the .pbix with the relevant tables and measures involved:  https://drive.google.com/file/d/1_w01kt7uQVoCQ0UTf2tMiOxZamAzWosU/view?usp=sharing

 

Any help understanding this is GREATLY appreciated,

Thanks,

..bob

5 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    3 years ago

    BobMcC You could achieve the same in one measure using VAR's:

    WA LTV Locked All = 
  IF(
    SUM('dmLoanInfo'[LoanAmount]) > 0 ,
        VAR __1 = SUMX('dmLoanInfo',[LTV]*[LoanAmount])
        VAR __2 = SUM('dmLoanInfo'[LoanAmount])
        VAR __3 = __1 / __2
      RETURN
        __3,
      BLANK()
  )

    The difference is that you are dividing your numerator once versus in the original, you are dividing the numerator by the demoninator for each row and then summing all up.