Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
Hi @Anonymous ,
Based on your description, you can do some steps as follows.
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.
Hi @Anonymous ,
Based on your description, you can do some steps as follows.
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.
@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.
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.
@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?
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
118 | |
107 | |
70 | |
70 | |
43 |
User | Count |
---|---|
148 | |
104 | |
104 | |
89 | |
66 |