topic Re: Measure Returning in DAX Commands and Tips

Measure Returning

BobMcC — Tue, 16 May 2023 17:24:48 GMT

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

Re: Measure Returning

Greg_Deckler — Tue, 16 May 2023 17:48:58 GMT

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.

Re: Measure Returning

BobMcC — Tue, 16 May 2023 18:20:14 GMT

Greg_Deckler

Hi Greg,

Thanks for the quick reply! I am unable to duplicate the results from the original version in SQL. Nor can I output the drill down detail and arrive at the same result in a spreadsheet.

https://docs.google.com/spreadsheets/d/11YpK6CVuATibrwpIz1fwCdocUlWWeKkN/edit?usp=sharing&ouid=104714542534674252376&rtpof=true&sd=true

Everthing proofs out to what is coming back from the new measure ( using the other 2 new measures )

I guess my question boils down to this... Is the original measure correct for Weighted Average and if so, How can this be written in SQL? The attached link has the spreadsheet I was using to work through this.

Breaking out the LTV * LoanAmount Line by line and Totaling,

then taking that TOTAL and dividing by LoanAmount TOTAL returns the results from the 2nd measure as well as my SQL Query results. Thanks for the help on this Greg!

Re: Measure Returning

Greg_Deckler — Tue, 16 May 2023 18:29:10 GMT

BobMcC This is my best understanding of how to create a Weighted Average.

Better Weighted Average per Category - Microsoft Power BI Community

Re: Measure Returning

BobMcC — Tue, 16 May 2023 18:54:08 GMT

Greg_Deckler

Thanks for the link Greg. Just watched the clip. Ultimately, it looks like your original post

aligns with his version of the Better Weighted Average Per Category

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()
  )

If that is the case, would you agree that the original measure is incorrect and should be changed to the version you sent? Not asking you to make a ruling 🙂 just asking for your opinion. Thanks!

btw: I did vote for the idea per your tag line link.

Re: Measure Returning

Greg_Deckler — Tue, 16 May 2023 19:46:37 GMT

BobMcC That would be my opinion, yes.