Forum Discussion

lreinhard77's avatar
lreinhard77
Frequent Visitor
3 years ago

Calculation inventory values

Hi, 

I want to calculate inventory values as exemplified in the following screenprint. 
My problem is related to the issue price calculation for which I need the **bleep** value and **bleep** qty from the previous row. 
I already tried many things but always get a circular dependency message. 
Does anyone has a tip how to fix this issue and can help me with the correct formula for the issue price calculation?

 

calculation
inventory value

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi lreinhard77 ,

    I have created a simple sample, please refer to my pbix file.

    Create a measure.

    Measure = 

CALCULATE(SUM('Table'[qTY]),FILTER(ALL('Table'),'Table'[date]<=SELECTEDVALUE('Table'[date])&&'Table'[Item]=SELECTEDVALUE('Table'[Item])))

     

     

    How to Get Your Question Answered Quickly 

     

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

     

    Best Regards
    Community Support Team _ Rongtie

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

     

    • lreinhard77's avatar
      lreinhard77
      Frequent Visitor
      3 years ago

      Hello, 

      I tried to make things more clear and prepared some data in Excel. 
      In short: 
      I get some data from an accounting system (yellow colored section). 
      For those data, I want to calculate (a) the issue price because I need it to ultimately calculate (b) the inventory value. 
      The inventory value is illustrated in the last column. 

      My problem is that the sales price and sales value (qty * sales price) influences the subsequent sales price calculations. 
      As an example, the sales price in line 4 is influenced by the sales price (times the sales quantity) in line 2
      Unfortunately, I don't know the correct way how to make this calculation in PowerBI. 

      I already tried many things but always get a circularity error 😞






      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Hi lreinhard77 ,

        Sorry, I'm not smart. Are you trying to calculate the sales pricez column? If according to you 427.5=(0+17100)/40, which column does 40 represent?Cumcurrenh or CurrentBuy?Why is the third column 0?(0-2137.5)/40=53.4375 and how do you get the 55 in (0+17100-2137.5+0+0+0+8100)/55? And why does this result appear in the fourth and fifth row? What is the logic of the calculation?

         

        How to Get Your Question Answered Quickly 

         

        If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

         

        Best Regards
        Community Support Team _ Rongtie

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