Forum Discussion

GOsorioB's avatar
GOsorioB
Regular Visitor
3 years ago

Problem getting UNIT COGS for Each SKU , Running totals for multiple categories of SKUs

Hi there: I'm trying to get the "Unit COGS by SKU" for an inventory transaction query that also helps me to get the net profit of each product for each sales transaction. 

 

The Inventory Query presents all inputs and outputs transactions that affects overall COGS for each SKU.

In order to properly obtain the COGS for each SKU, I grouped items, locations and batch into a single category named : "Item-Inst Category". Transactions for each category are grouped so each input and output that affects COGS forms a set of items that enables the calculation of running totals for units and cost. In this calculation of running totals is where I need help. I created a quick measure to enable accruals of units ("

Net_Inventory_Units running total in Combi Item-Inst Category") and Costs ("

Transactions in USD running total in Combi Item-Inst Category 2").

 

Then my next step was creating calculated columns with the measures mentioned before so I can have a Unit COGS in each transaction and category (Dividing accrued cost over accrued units), but accruals are not correct and calculated columns brought “circular dependency errors”.

 

I greatly appreciate any help on these errors I reviewed all post regarding COGS but I cannot figure out the errors and many of the examples only refers to single SKUS or  to one category but not  queries with many categories or SKU’s.

 

I uploaded the PBI file to https://drive.google.com/file/d/1OWBKtGlTqCIyUswWr5oX0tNYMtIp3TUq/view?usp=sharing and also an excel file (https://docs.google.com/spreadsheets/d/1OagKyE7bKRN3w3DN-gu56UoQ3LJqxgV-/edit?usp=sharing&ouid=102055228212520981889&rtpof=true&sd=true )  that shows the model I want to replicate in Power BI.

 
Thanks in advance for any help or guidance and kind regards.
 
German
Need Help

3 Replies

  • lucadelicio's avatar
    lucadelicio
    Icon for Impactful Individual rankImpactful Individual
    3 years ago

    Hi GOsorioB,

    i'll try to help you.

    Many of the DATATYPE in your main dataset table are wrong.
    The DATE filed in text and it has to be DATE.
    The unit column are Currency with the dollar $ and it will be INTEGER.
    The relationship between the exchange values has no sense beacause is not deterministic (how you decide the date in wich an import is be used if the import is present in more than one date?).
    You already have the import of the excahnge in your main table, why you created this relationship?
    Please explain me the logic with you need to calculate the COGS for each SKU and i will do it for you.
    Try to be clear and confused.
    Try to work on the reviewed report download it at this link:
    https://we.tl/t-DYaFQhJf1q

    I attend your details.

    Regards,

    • GOsorioB's avatar
      GOsorioB
      Regular Visitor
      3 years ago

      Hi Luca, first of all, thanks in advance for your help, I appreciate it.

       

      Regarding your inquiries I will try to  explain them as follows:

      • You are right with Datatypes, I corrected them in an updated file that will be enclosed.
      • Exchange values were calculated for Imports, adjustments and returns transactions in the power query tables ( See PDF file that explains the procedure),  currently in main data set (renamed to Kardex)  they are used only as a reference at row level for each transaction. I removed the relationship in power bi. Initial stok balance has also their exchange rates and converted to USD.
      • Having Unit COGS for each transation  at a row level (COGS Accruals / Units accruals) I will have a calculated Unit cogs IN USD for every transaction, in special for those  marked as "Remisiones" in "Dcument Description" Column. Those are requisitions.
      • The Unit COGS for every product at row level will be cross-referenced later with a detailed sales table using the "Remisiones" unique document number in order to obtain cost and margin for every product   at a row level, using a unique Key that reference items,dates, batchs,warehouses and quantities.

      I made a PDF document with all steps made and that I hope would explain what I´m tryng to achive.

       

      Again thanks for helping me with this project and please let me know if I made myself a bit clearer with my explanations.

       

      Best regards,

       

      German Osorio