Calculation not working when adding a Column Sort

[jamoroso]

I am calculating the % of Originations based on the Loan Credit Score within the Category. It is working fine (left side below) until I add a column sort in. I added sorting the LoanCreditScore by the column CreditScoreRangeSortOrder and all the % Origination columns calculated to 100% (right side below). I cannot figure out how to get the % Origination calculation to work with the sorted column.

Measures:

Originations = CALCULATE(SUM('Loans'[LoanOriginalBalance]),DATESBETWEEN('Loans'[LoanOpenDate],SELECTED

VALUE('Date'[MonthBeginDate]),SELECTEDVALUE('Date'[MonthEndDate])))

% Originations = DIVIDE(Loans[Originations],CALCULATE(Loans[Originations],ALLSELECTED((Loans[LoanCreditScore]))))

(using 06/30/2023 as the MonthEndDate)

  

Date Data below 

Loan data and PBIX file: https://we.tl/t-awOXdcxiby

Thank you! 

--Janet

[Ashish_Mathur · Accepted solution]

Hi,

Please find attached the PBI file.

Hope this helps.

[jamoroso]

@Ashish_Mathur 

Thank you so much!

[Ashish_Mathur]

You are welcome.

[ThxAlot]

There exists a side effect of Sort by Column functionality; this hidden pitfall impacts the calculation.

Refer to this article for details:

Side effects of the Sort By Column setting in DAX - SQLBI 

[foodd]

Please provide your work-in-progress Power BI Desktop file (with sensitive information removed) not as a screenshot.

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.

[jamoroso]

Dates

DateKey	FullDate	Latest Month End	MonthBeginDate	MonthEndDate	MonthEndDateKey
20230131	1/31/2023	1/31/2023	1/1/2023	1/31/2023	20230131
20230228	2/28/2023	2/28/2023	2/1/2023	2/28/2023	20230228
20230331	3/31/2023	3/31/2023	3/1/2023	3/31/2023	20230331
20230430	4/30/2023	4/30/2023	4/1/2023	4/30/2023	20230430
20230531	5/31/2023	5/31/2023	5/1/2023	5/31/2023	20230531
20230630	6/30/2023	6/30/2023	6/1/2023	6/30/2023	20230630