measure

[SzymonKl]

 Hi All 

Can you please help me create a measure please : 

Currently my stock balance ( measure [Processed Stock] for asset type [ASSET_TYPE_ID] = 128 is 1323, for asset type [ASSET_TYPE_ID] = 127 is 3965 and for asset type [ASSET_TYPE_ID] = 126 is 3600.

the balances for [ASSET_TYPE_ID] = 127 and 126 are corect( dont make any ajustments). I need to summarise these two balances and for every 9 units subtract one unit from [Processed Stock], where [ASSET_TYPE_ID] = 128.

that means 3965+3600 = 7565 /9 = 840.55 so subtract 840 from [Processed Stock] where [ASSET_TYPE_ID] = 128 

Any idea how to do this ? 

[MFelix]

Hi @SzymonKl ,

This is very generic however you can try something similar to this:

Stock Revised =
IF (
    SELECTEDVALUE ( 'Table'[ASSET_TYPE_ID] ) = 128,
    SUM ( 'Table'[Stock] )
        - SUMX (
            FILTER ( ALL ( 'Table' ), 'Table'[ASSET_TYPE_ID] <> 128 ),
            'Table'[Stock]
        ),
    SUM ( 'Table'[Stock] )
)

[SzymonKl]

Hi Miguel

Unfortunately, this is not working. 

'Table'[Stock]

 is a measure not column, and the data is connected via direct query 

[MFelix]

Hi @SzymonKl ,

If this is a measure then just replace the column by the measure:

Stock Revised =
IF (
    SELECTEDVALUE ( 'Table'[ASSET_TYPE_ID] ) = 128,
    [Stock] 
        - SUMX (
            FILTER ( ALL ( 'Table' ), 'Table'[ASSET_TYPE_ID] <> 128 ),
            [Stock]
        ),
[Stock]
)