Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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!
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.
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.
Proud to be a Super User! | |
User | Count |
---|---|
25 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
27 | |
13 | |
11 | |
9 | |
6 |