Forum Discussion

PowerBIUser007's avatar
PowerBIUser007
Regular Visitor
1 year ago

Filter a Matrix table by first row

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!

 

2 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Icon for Super User rankSuper User
    1 year ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.