Forum Discussion
Filter a Matrix table by first row
Hi PowerBIUser007 - You'll need to create a measure that calculates the CM% for the customer level and use that measure to control your filter.
for example:
Customer CM% Filter =
CALCULATE(
[CM%],
ALLEXCEPT('YourTable', 'YourTable'[Customer])
)
In your matrix visual, place Customer in the rows, then expand it to include Cost Center. Also, put your measures for Turnover, Contribution Margin, and CM% in the Values field.
You can now apply a visual-level filter to your matrix visual to only display customers where the Customer CM% Filter is less than 5%.
Go to the "Filters on this visual" pane in Power BI.
Drag your Customer CM% Filter measure to the filter area.
Set the filter to less than 5.
in your CM% measure too
CM% =
IF(
ISINSCOPE('YourTable'[CostCenter]),
DIVIDE([Contribution Margin], [Turnover]),
[Customer CM% Filter] -- Use the customer level measure here
)
This way, when you're at the customer level, you'll see the filtered customer CM% (using Customer CM% Filter), but at the cost center level, you'll still see the individual cost center's CM%.
I hope , by using the above approach,we can perform the customer-level filtering without removing cost center details.