Forum Discussion

sPowerBi's avatar
sPowerBi
Frequent Visitor
8 years ago

Calculate NPI (non-productive Inventories in DAX

Hi, I want to calculate NPI ( non productive Inverntories) on power bi. I have stock movements table like this. Date Product Code Quantity Price 1.01.2018 B 5 10 2.01.2018 B 6 10 ...
v-huizhn-msft's avatar
v-huizhn-msft
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

Hi sPowerBi,

What's your calculation rules? How did you get the following column? Please list an example for more details, otherwise we can not help you.



Thanks,
Angelia

sPowerBi's avatar
sPowerBi
Frequent Visitor
8 years ago

Hi,

This  is movement table. And total stock quantity equal 468 on 11 January 2018. 

If the quantity is less than zero then the tansaction is the sales movement. If the quantity is greater than zero then the transaction is the buy movement. so I want to find which buying movements the stock consist of. I find out which buying movements by reducing Quantity of movement from total stock

I hope i can tell.

Thanks

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi sPowerBi,

    But how to get 210 when quantity 345>0?

    Thanks,
    Angelia

    • sPowerBi's avatar
      sPowerBi
      Frequent Visitor
      8 years ago

      Hi,

       

      Yes 345>0 but remaining stock is 210. I want calculate row by row like this;

      if (  transaction  quantity  > 0 )

      {

      if ( remaining stock > 0 and remaining stock > quantity) 

             result : quantity

      else 

           result : remaining stock

      }

       

      Thanks

      • v-huizhn-msft's avatar
        v-huizhn-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        8 years ago

        Hi sPowerBi,

        In your thread, there is no [remaining stock] column, is it a measure? Or it's a column in resource table? Could you please share more details?

        Thanks,
        Angelia