Measure returning unexpected value

[Quite_lost]

Hi, I quite new to PowerBI and DAX structure and need some help.

I am trying to make an Inventory History dashboard that has a measure Average Sales by month in the context of shortage of stock of the product. The period when there is a shortage of "Product A" must not be considered in the average, thus "Average SAles by Month "in stock".

The problem is: the expected result differs from what is shown. For an example, in the period between 01/10/2019 to 30/09/2020 (dd/mm/yyyy) the expect average would be 18930, not 17633.

Also, how do I upload the dummy file in here?

Any help will be much appreciated.

Best wishes.

[amitchandak]

@Quite_lost , can you share formulas of three involved

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Quite_lost]

Here is the pbix file.

Here are the formulas I'm using:

#Total Quantity = SUM('Stock History'[Quantity]) *-1

#Cumulative Total = CALCULATE( [Total Quantity], FILTER( ALL( Dates[Date] ), Dates[Date] <= MAX( Dates[Date] ) ) )

#Days "in stock" = COUNTROWS(FILTER(ADDCOLUMNS(CROSSJOIN( VALUES( Dates[Date] ), VALUES('Stock History'[Product]) ), "Inventory Levels", [Cumulative Total] ), [Inventory Levels] <> 0 && NOT( ISBLANK( [Inventory Levels] ) ) ) )-1

#Days "zero stock" = COUNTROWS( FILTER( ADDCOLUMNS( CROSSJOIN( VALUES( Dates[Date] ), VALUES('Stock History'[Product]) ), "Inventory Levels", [Cumulative Total] ), [Inventory Levels] = 0 && NOT( ISBLANK( [Inventory Levels] ) ) )

#Months "in stock" = [Days "in stock"] / 30.41666

#Average Sales by Month "in stock" = [Total Quantity] / [Months "in stock"]

[V-lianl-msft · Accepted solution]

Hi @Quite_lost ,

Based on your description, I checked your example pbix and found the problem.

The reason is that you turn off the interaction between Movement Type slicer and Days "in stock".

Correspondingly, you need to clear the filter of Movement Type in the calculation.

Average Sales by Month "in stock" = var t = [Total Quantity]
var m = CALCULATE([Months "in stock"],ALL('Stock History'[Movement Type]))
return DIVIDE(t,m)

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Quite_lost]

Wow, that really worked!

Thank you very much!

I need to learn a lot more about filter and contexts.