Forum Discussion
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 measuresRevenue-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.
Hi swinings , your suspicion is exactly right. The issue occurs because measures in Power BI don’t have a row-level context on their own, they’re aggregated dynamically.
So when you try to “Sort by Column,” Power BI can’t anchor that sort order to a measure (like Net Operating Revenue), since measures don’t exist in the model’s static table structure.
🧩 Fix Option 1: Create a Sort Helper Column
In your table (the one with Group values like Revenue, Expenditure, Beginning Cash Balance),
create a new column named something like Order:Order = SWITCH( 'YourTable'[Group], "Revenue", 1, "Expenditure", 2, "Beginning Cash Balance", 3, 4 )Then go to the Data view → select Group → under the Column tools ribbon, choose Sort by Column → Order.
Now your table visual will respect the desired order , and your measure (Net Operating Revenue) will stay visible.
⚙️ Why This Works
The sort-by-column operation needs a categorical column to define order, not a measure. By introducing a static numeric “Order” column, Power BI can render your table’s rows in that sequence while still evaluating measures like Net Operating Revenue in context.
Tip
If you need Net Operating Revenue (a calculated row, not just a measure) to appear as part of the same table visual:
Use UNION or DATATABLE in a small supporting table to append that label (e.g., “Net Operating Revenue”) with its own sort order.
Then your measure can reference it using a conditional DAX expression, something like:
Actuals = SWITCH( TRUE(), 'Groups'[Group] = "Revenue", [RevenueMeasure], 'Groups'[Group] = "Expenditure", [ExpenditureMeasure], 'Groups'[Group] = "Net Operating Revenue", [NetOpRevenueMeasure], 'Groups'[Group] = "Beginning Cash Balance", [BeginningCashBalanceMeasure] )
That way, everything including your calculated row, will appear in the correct sequence, sorted by the Order column.
⭐Hope this solution helps you make the most of Power BI! If it did, click 'Mark as Solution' to help others find the right answers.
💡Found it helpful? Show some love with kudos 👍 as your support keeps our community thriving!
🚀Let’s keep building smarter, data-driven solutions together!🚀 [Explore More]