topic Re: Filter a Matrix table by first row in DAX Commands and Tips

Filter a Matrix table by first row

PowerBIUser007 — Thu, 10 Oct 2024 20:07:28 GMT

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!

Re: Filter a Matrix table by first row

rajendraongole1 — Sun, 13 Oct 2024 03:40:48 GMT

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.

Re: Filter a Matrix table by first row

Anonymous — Mon, 14 Oct 2024 05:58:31 GMT

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.