Forum Discussion

SzymonKl's avatar
SzymonKl
Helper I
2 years ago

measure

 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 ? 

Need Help

3 Replies

  • MFelix's avatar
    MFelix
    Super User
    2 years ago

    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's avatar
      SzymonKl
      Helper I
      2 years ago

      Hi Miguel

      Unfortunately, this is not working. 

      'Table'[Stock]

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

      • MFelix's avatar
        MFelix
        Super User
        2 years ago

        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]
)