Using FILTER with CALCULATE for [MAT Margin] measure

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

Hi @Timmay,

Give this a go

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

IF( NOT( ISBLANK( [Sales Margin %] )),
DIVIDE( ( MATSales - MATCogs ), MATSales, 0 ) // % margin for MAT Sales
)
``````

I hope this is helpful

2 Likes

works a treat thanks @Melissa

Looks like I’m using the wrong formula for the result that I’m after. No need to FILTER when I can just simply use an IF statement.

Thank you!
Tim

1 Like