Remove filter in DAX

DAX DAX Calculations
1

Hi, Everyone,

Hope you all have a great Black Friday weekend!

I am having a a bit challenge to remove filter cross tables. Attached you will see my sample file and data model, which I prefer to stay the same.

The question is to calculate the sell-through rate of a product and measure the channel performance based on sell-through rate. Here are the definition of the measures:

Units Sold = Sum of Sold units of all channels in each week
Opening Inventory = Current inventory + Units Sold
Sell-through rate = Units Sold / Opening Inventory * 100%

My data model and DAX work well for most of what I need shown below.
image

The problem is that when I interact with the visualization, I noticed the problem. When I highlighted the first row in the second table, The opening inventory for SKU1 in the first table became 370, instead of 416.

image

It there anyway in PowerBI to solve this problem using DAX or other method?

Thanks!

Mike
Sell-through Inventory.pbix (33.8 KB)

2

Hi @Mike

Please use a new measure on sell-through rate by sale table like this below

Please let me know if it helps for the problem you explained

Opening Inventory_sku = CALCULATE(SUM(Inventory[Current Inventory]) + ‘Key Measures’[Units Sold], ALL(Sales[Channel]))

image
image3739×1326 319 KB

Sell-through Inventory1.pbix (34.1 KB)

4

Thanks @Dhrubojit_Goswami , I replaced my Opening Inventory measure with your new measure and it works as I expected.

Thanks a lot for your help!

Mike

1 Like
5

@Mike

I am happy that I was able to help you , would you please mark the answer as solution so that any one else with similar problem can find it easily.