Measure using current inventory measure (response)

[Anonymous]

This is a follow on from my other post of "Measure using current inventory measure" (can't seem to respond to the thread there, so starting a new one and clarifying. @amitchandak here's my reply to your reply.)

Hi, I'm currently working on an inventory report, so I have a main table with inventory movement posting and also a date table. There are various attributes for each entry, such as location (plant), material, storage type (jumbo/pallet), etc. I need a measure that will calculate the monthly % average of inventory stored in jumbo vs pallet.

For each month, the calculation would be as follows for % pallet: (sum of daily inventory levels stored on pallets)/(sum of total daily inventory stored on pallets and jumbo). The same for % jumbo.

Here's the link to PBI file with sample data in "posting table". My procedure to get the % pallet and jumbo calculations are as follows:

1) Wrote the following measure that calculates inventory on hand from posting table:

Inventory on hand = 
var _currdate=MAX('Date Table'[Date])
return

CALCULATE(
    SUM('Table'[Quantity]),
    FILTER(
        ALLSELECTED('Date Table'[Date]),
        ISONORAFTER('Date Table'[Date], _currdate, DESC)
    )
)

2) Created a matrix visualization found on page 1.

3) Exported the data from the matrix, re-inserted into the PBI in "Inventory on date" table, and manipulated the data.
4) Exported the "inventory on date" table

5) Manually summed all the days with inventory for each month of Oct, Nov, Dec for each plant, for jumbo and pallet separately. Calculation was done in excel and here's the file.

End result desired is bolded:

	Plant	Total Jumbo	Total Pallet	Total	% Jumbo	% Pallet
Oct	5320	1915.9	462	2377.9	80.6%	19.4%
Nov	5320	4141.5	1980	6121.5	68%	32%
Dec	5320	138320	3494	141814.1	97.5%	2.5%
Oct	5323	279	1080	1359	20.5%	79.5%
Nov	5323	870	2556	3426	25.4%	74.6%
Dec	5323	2594	3566.48	6160.48	42.1%	57.9%
Oct	5325	0	324	324	0%	100%
Nov	5325	480	756	1236	39%	61%
Dec	5325	4476	2347.8	6823.8	65.6%	34.4%
Oct	5328	1700	284.4	1984.4	85.7%	14.3%
Nov	5328	3000	1185	4185	72%	28%
Dec	5328	1776	191.4	1967.4	90.3%	9.7%

Hope this explains the procedure, sorry if it wasn't clear before. 

[v-yuaj-msft · Accepted solution]

Hi @Anonymous ,

Based on your description, you can do some steps as follows.

1. Create two calculated tables.

Case =

SUMMARIZE (

    'Posting Table',

    'Posting Table'[Posting date],

    'Posting Table'[Plant Code],

    'Posting Table'[Storage Type],

    "Quantity",

        CALCULATE (

            SUM ( 'Posting Table'[Quantity] ),

            ALLEXCEPT (

                'Posting Table',

                'Posting Table'[Posting date],

                'Posting Table'[Plant Code],

                'Posting Table'[Storage Type]

            )

        )

)




Table =

ADDCOLUMNS (

    CROSSJOIN (

        ALL ( 'Date Table'[Date] ),

        ALL ( 'Posting Table'[Plant Code], 'Posting Table'[Storage Type] )

    ),

    "amount",

        LOOKUPVALUE (

            'Case'[Quantity],

            'Case'[Posting date], [Date],

            'Case'[Plant Code], [Plant Code],

            'Case'[Storage Type], [Storage Type]

        )

)

2. Create a column to calculate the amount and a column to mark month.

new_amount =

CALCULATE (

    SUM ( 'Table'[amount] ),

    ALLEXCEPT ( 'Table', 'Table'[Plant Code], 'Table'[Storage Type] ),

    'Table'[Date] <= EARLIER ( 'Table'[Date] )

)




month =

MONTH ( 'Table'[Date] )

3. Create a calculated column to calculate the percentage of each storage type.

%percent =

VAR x1 =

    CALCULATE (

        SUM ( 'Table'[new_amount] ),

        ALLEXCEPT (

            'Table',

            'Table'[month],

            'Table'[Plant Code],

            'Table'[Storage Type]

        )

    )

VAR x2 =

    CALCULATE (

        SUM ( 'Table'[new_amount] ),

        ALLEXCEPT ( 'Table', 'Table'[month], 'Table'[Plant Code] )

    )

RETURN

DIVIDE ( x1, x2 )

4. Create a matrix.

Result:

Hope that's what you were looking for.

Best Regards,

Yuna

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

[Anonymous]

@v-yuaj-msft 

Hi Yuna, that works, thanks!

[Anonymous]

@ryan_mayu In my final report, I will only have the "posting table" and "date" table. I basically need a measure that will give me the desired result of % pallet and % jumbo using only the data in the "posting table". I cannot create an "inventory on date" table.
I have only included the "inventory on date" table to calculate % pallet and % jumbo in the only way that I know. 

[ryan_mayu]

@Anonymous 

Is this what you want? please see the attachment file below

[Anonymous]

Hi @ryan_mayu , the measures you used do get the correct % values, but I need a measure to calculate the information in the "Inventory on date" table as well. I can't use the information in that table for the actual report or for subsequent measures because it's a manually created table (see step 3). I only created it to show you what I did to get to the desired % pallet and % jumbo calculations.

[ryan_mayu]

@Anonymous 

still not clear about your request. How to insert the matrix to the inventroy table?

Do you mean transfer the posting table to inventory on date table?

The current inventory on date table is the expected result?