Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I've been trying to proof out a .pbix. I'm having an issue with one of the measures.
The measure name is
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
Solved! Go to Solution.
@BobMcC That would be my opinion, yes.
@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.
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.
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!
@BobMcC This is my best understanding of how to create a Weighted Average.
Better Weighted Average per Category - Microsoft Power BI Community
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.
@BobMcC That would be my opinion, yes.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
46 | |
28 | |
23 | |
12 | |
8 |
User | Count |
---|---|
76 | |
51 | |
45 | |
16 | |
12 |