Hi all,

My powerbi file is here: https://www.dropbox.com/s/v3w9jdak56zngyy/Mock%20Data%20PBI.pbix?dl=0

I’m comparing product margins for a given month with moving annual total (MAT) margin calculation. Issue is that not all products have sales for the month and therefore show blank sales margin %.

I’d like to automatically filter out the Sales margin % blank rows with DAX on the [MAT Margin] column.

One can filter these out manually, but that’s not a very DAX way of doing things is it?

I’ve tried to code a formula to filter out rows where sales margin % is 0 but I get unintended results:

In the above screen shot the column “MAT margin with filter” is the same as “sales margin %”, even though the DAX formula is:

```
MAT Margin With Filter =
CALCULATE( [MAT Margin] , FILTER(Sales, [Sales Margin %] <>0))
```

as what I want to do is change the context of [MAT Margin] whenever rows of [sales margin] are not equal to 0

I’m trying to break down what i’m attempting to do here. Essentially I’m trying to filter the sales table & take out any calculations of sales margin not equal to 0. I’m assuming though because of the time intelligence function used in [MAT Margin] this is not working with the filtered table? but why then are the results the same as sales margin?

MAT margin DAX is:

```
MAT Margin =
VAR
MATSales = CALCULATE( SUM(Sales[Sales]) , DATESINPERIOD('Date Table'[Date] , LASTDATE( 'Date Table'[Date]), -1, YEAR))
//moving annual total sales
VAR
MATCogs = CALCULATE( SUM(Sales[Cost]) , DATESINPERIOD('Date Table'[Date] , LASTDATE( 'Date Table'[Date]), -1, YEAR))
//moving annual total cost of goods sold
RETURN
DIVIDE((MATSales - MATCogs) , MATSales , 0)
// % margin for MAT Sales
```

What is the way to approach this?

Thanks,

Tim