DAX: Exclude where Measure = 0 in certain Row context, within another Measure (.PBIX Included)

[Anonymous]

Hi guys,

I try to calculate an average price for material stocks.

I simply base this on the total value of the stock divided by the total volume of the stock.

Value per TON = 
'Fact Inventory'[Closing Stock Value]/'Fact Inventory'[Closing Stock]

However, not all Materials in my dataset have a Value. Whenever they do not have Value information, I want to exclude these Stock Volumes from the calculation within 'Value per TON' Measure.

File that includes all:

.PBIX File 

Below you can see the measure does a correct way of calculating the average in the above matrix, but in the matrix below it adds also the quantity (2000) where there isn't any 'Closing Stock Value', resulting in an average of 577.83 instead of the expected 731.14.

So basically I want to exclude the 'Closing Stock' where on Batch,Material,YearMonth-level the quantity = 0.

This the measure and an underlying measure that's needed for it:

Closing Stock = 
 TOTALMTD(
 CALCULATE(
 SUM('Fact Inventory'[Quantity]),
 'Fact Inventory'[Source] = "Opening Stock"),
 'Dim Calendar'[Date]) 
 + [MTD Change in valuated stock level]

MTD Change in valuated stock level = 
CALCULATE(
SUM('Fact Inventory'[Quantity]),
DATESMTD('Dim Calendar'[Date]),
'Fact Inventory'[Source] = "movements")

And below I have the sample data, also included in the file:

1	A	101	950	150	142500	8/1/2021	Movements
1	A	101	950	20	19000	8/2/2021	Movements
2	A	102	700	800	560000	8/3/2021	Movements
2	A	102	700	75	52500	8/4/2021	Movements
2	A	103		1000	0	8/4/2021	Movements
3	A	104	600	30	18000	8/4/2021	Movements
3	A	104	600	40	24000	8/4/2021	Movements
3	A	104	600	35	21000	8/4/2021	Movements
1	A	101	950	1000	950000	8/1/2021	Opening Stock
2	A	102	700	5000	3500000	8/1/2021	Opening Stock
2	A	103		1000	0	8/1/2021	Opening Stock
3	A	104	600	5	3000	8/1/2021	Opening Stock
1	A	101	950	1170	1111500	9/1/2021	Opening Stock
2	A	102	700	5875	4112500	9/1/2021	Opening Stock
2	A	103		2000	0	9/1/2021	Opening Stock
3	A	104	600	110	66000	9/1/2021	Opening Stock

Do you guys have any suggestion how to change the measure accordingly?

[v-kkf-msft]

Hi @Anonymous ,

Please try the following formula:

Measure = 
IF (
    ISFILTERED ( 'Fact Inventory'[Batch] ),
    [Value per TON],
    AVERAGEX (
        FILTER ( 
            ALLSELECTED ( 'Fact Inventory'[Batch] ), 
            [Closing Stock Value] <> 0 
        ),
        [Value per TON]
    )
)

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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

[MFelix]

Hi @Anonymous 

Try the following code:

Value per TON = 
DIVIDE([Closing Stock Value], CALCULATE([Closing Stock], FILTER(ALLSELECTED('Fact Inventory'),[Closing Stock Value] <> 0))