REMOVEFILTERS Function DAX

REMOVEFILTER function is a Power BI filter function in DAX, It removes filters from the specified tables or columns.

It can only be used to remove filters but not to return a table.

SYNTAX

REMOVEFILTERS([<table> | <column>[, <column>[, <column>[,…]]]])

Lets take a look at using REMOVEFILTERS function in Power BI.

Here we have a sample dataset named ItemDetails as shown below.

Lets create a simple report that display the total quantity sold by item and color as shown below.

Lets assume that, you want to calculate the percentage of sales contribution by each color for individual item, so to do that you need to divide sales quantity of each color within individual item with over all sales quantity of that individual item.

You also know that filter context is also applied on Item and Color level in above report, that’s why it returns a sales quantity by Item and Color, so to get the total sales for individual item regardless color, you need to remove filter context on color level, that you can do using a REMOVEFILTER function as shown below.

OverAllSales =

CALCULATE ( SUM ( ItemData[Sales_Qty] ), REMOVEFILTERS ( ItemData[Color] ) )

After creating a measure, lets drag the measure into report to see the output.

You can see REMOVEFILTES function removes the filter context from Color column and returns the over all sales of an individual Item against each rows of color.

Now to calculate the percentage of sales contribution, you need to use DIVIDE function which will divide sales quantity of each color with over all sales of an individual item that is returned by DAX OverAllSales . 

Lets create another DAX named SalesContribution% as shown below.

SalesContribution% =
DIVIDE( SUM(ItemData[Sales_Qty]), [OverAllSales])

Lets drag this measure into report to see the output.

You can see, for individual item the total percentage of sales contribution is 100% and each color has some sales contribution which makes the total percentage of sales contribution as 100%.

So far you have seen, total sales contribution by each color for individual item.

Lets see the total sales contribution by each item in over all Item sales. If you collapse the matrix visual at item level in order to see the percentage of sales contribution of each item in over all sales, you will see that it displays 100% sales contribution by each item in over all sales, which is clearly wrong, how it could be possible that each individual item has contributed as 100 % of over all sales.

So to show correct contribution of each individual item in over all item sales you need to remove filter context on Item Level.

Lets quickly create a measure OverAllSalesByItem , which returns over all sales by Item against each rows of items, following measure uses a REMOVEFILTERS function which removes the Item Level filter context.

OverAllSalesByItem =
CALCULATE ( SUM ( ItemData[Sales_Qty] ), REMOVEFILTERS ( ItemData[Item]) )

SalesContribution%_BYItem =
DIVIDE( SUM(ItemData[Sales_Qty]), [OverAllSalesByItem])

You can see, the sales contribution by individual item to over all item sales, and you can also note that the total sales contribution of each individual item makes the total sales contribution as 100 %.




Also Read..

ALL

CALCULATETABLE

SELECTEDVALUE

FILTER

 10,905 total views,  1 views today