Filtering a line chart

[Anonymous]

Hi Everyone,

I’m just starting with Power BI and have a problem with a line chart that I hope someone will be able to help with.

The chart shows Sales Margin by Employee/Month and is filtered by Customer, Employee and Job Date.

I have a measure to calculate Margin as follows

Margin = IF(ISBLANK( [Profit]/SUM('Job Ledger'[Total Price])),0,[Profit]/SUM('Job Ledger'[Total Price]))

It has the IF ISBLANK replace with 0 as I want to show a data point for each month for the employee.

This works fine except when I use SELECT ALL in the Employee slicer and I get a line for every employee not just the ones who have been working on a job for the customer. I understand logically why it’s happening but I can’t seem filter the employees out don’t relate to that customer having tried various measures without success.

I’d be grateful for any advice on how I can do this.

Many thanks

Sample File : Example.pbix 

[tex628 · Accepted solution]

Hi @Anonymous ,

Try this:

Margin = 
IF(
ISBLANK( CALCULATE( [Profit]/SUM('Job Ledger'[Total Price]) , ALLSELECTED('Calendar'[Date])), BLANK(),
IF(
ISBLANK( [Profit]/SUM('Job Ledger'[Total Price])),
0,
[Profit]/SUM('Job Ledger'[Total Price])))

Let me know how it goes! 

Br, 
J

[Anonymous]

Hi

Thanks so much. Works perfectly and a valuable lesson for me.

Best regards