Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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

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:

1A1019501501425008/1/2021Movements
1A10195020190008/2/2021Movements
2A1027008005600008/3/2021Movements
2A10270075525008/4/2021Movements
2A103 100008/4/2021Movements
3A10460030180008/4/2021Movements
3A10460040240008/4/2021Movements
3A10460035210008/4/2021Movements
1A10195010009500008/1/2021Opening Stock
2A102700500035000008/1/2021Opening Stock
2A103 100008/1/2021Opening Stock
3A104600530008/1/2021Opening Stock
1A101950117011115009/1/2021Opening Stock
2A102700587541125009/1/2021Opening Stock
2A103 200009/1/2021Opening Stock
3A104600110660009/1/2021Opening Stock

 

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

Need Help

2 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    4 years ago

    Hi Anonymous 

     

    Try the following code:

     

    Value per TON = 
DIVIDE([Closing Stock Value], CALCULATE([Closing Stock], FILTER(ALLSELECTED('Fact Inventory'),[Closing Stock Value] <> 0))
  • v-kkf-msft's avatar
    v-kkf-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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.