Help with Sort by Order in Matrix

Hi swinings ,

I went through your scenario and was able to reproduce the exact issue the Net Operating Revenue row showing blanks or zeros after applying a custom sort using a Group table. The root cause is that when a relationship exists between the Group (sort table) and FactData, Power BI automatically filters the fact table based on each group value. Since the fact table only contains entries for Revenue and Expenditure, rows like Net Operating Revenue or Beginning Cash Balance have no corresponding data in the fact table, causing those measures to evaluate to blank and disappear or show incorrect totals after sorting.
Delete the relationship. Open Model view in Power BI Desktop. Find the relationship between Group[Group] and FactData[Group].Delete it the Group table should remain disconnected. It will act purely as a display and sort order table, not a filtering table. Then Apply custom sorting. In Data view, select the Group table. Click the Group column - Column tools - Sort by Column - select Order.

Create measures

Revenue-measure :

Revenue-measure =
VAR CurrentGroup = SELECTEDVALUE('Group'[Group])
RETURN
IF(
CurrentGroup = "Revenue" || CurrentGroup = "Net Operating Revenue",
SUM('FactData'[Revenue]),
0
)

Expenditure-measure :

Expenditure-measure =
VAR CurrentGroup = SELECTEDVALUE('Group'[Group])
RETURN
IF(
CurrentGroup = "Expenditure" || CurrentGroup = "Net Operating Revenue",
SUM('FactData'[Expenditure]),
0
)

Net Operating Revenue :

Net Operating Revenue =
VAR CurrentGroup = SELECTEDVALUE('Group'[Group])
VAR TotalRevenue = CALCULATE(SUM('FactData'[Revenue]))
VAR TotalExpenditure = CALCULATE(SUM('FactData'[Expenditure]))
RETURN
SWITCH(
TRUE(),
CurrentGroup = "Revenue", TotalRevenue,
CurrentGroup = "Expenditure", -TotalExpenditure,
CurrentGroup = "Net Operating Revenue", TotalRevenue - TotalExpenditure,
CurrentGroup = "Beginning Cash Balance", 0,
ISBLANK(CurrentGroup), TotalRevenue - TotalExpenditure,
BLANK()
)

The Group table defines a custom order but doesn’t filter the fact table. The measures use SELECTEDVALUE to explicitly handle the current group context. This approach gives you a financial statement style matrix where each line item is fully controlled. This structure keeps your report stable, avoids disappearing measures, and allows flexible expansion for lines like Ending Cash Balance or Net Income later.

Thank you.