Using FILTER with CALCULATE for [MAT Margin] measure

DAX DAX Calculations
1

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 %.

image

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?

image
image1210×711 47.5 KB

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

image

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

2

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
3

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