Filter a Matrix table by first row

[PowerBIUser007]

Hello everyone,
my first post as a fairly new powerbi user, I hope I'm in the right section of the forum.

I have the following request:
I have a table with customer names. Also 3 further measures with turnover, contribution margin and the contribution margin in % of turnover (CM%) for each customer.

I have stored the whole thing in a matrix table with the following arrangement:

Customer | Turnover | Contribution margin | CM %
Customer1 | 3425 | 342.5 | 10%
Customer2 | 2000 | 50 | 2.5%

I would also like to filter the whole thing so that only the CM% that is below 5% is displayed. Everything works great so far.

Now the problem: I would like to expand the customer into its individual cost centres as an additional line under the respective customer, like this.

- Customer 1
        13435251
        13526625
- Customer 2
        11763727
        16467245

The cost centre is also a table and it also works with the corresponding measures for each cost centre.

However, there is the case here that some customers are below the 5%, but the individual cost centres are above 5%. The filter mentioned above now filters out the cost centres so that the total figures change. However, I still only want to filter by customers and not by cost centres,

I have searched both here and on the internet for any information on how to filter only the first area, but unfortunately I have not been successful. I first tried another custom measure, like isinscope or calculate, but seem to be doing something wrong here. Is this even possible? Is it even possible?

I hope I have explained it clearly. Learning the DAX-Language is quite a task as a newcomer. 
If I should provide more info or anything, please let me know!
Thank you in advance!

[Anonymous]

Hi @PowerBIUser007 ,

Don't know much about your data model, but you can try the following expression:

CustomerCMFilter = 
IF (
    HASONEVALUE('Customer'[CustomerName]),
    IF (
        [CM%] < 0.05,
        1,
        0
    ),
    1
)

DisplayMeasure = 
IF (
    ISINSCOPE('Customer'[CustomerName]),
    IF (
        [CustomerCMFilter] = 1,
        [Turnover],  // Replace with the measure you want to display
        BLANK()
    ),
    [Turnover]  // Replace with the measure you want to display for cost centres
)

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[rajendraongole1]

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.