Converting Excel and Tally Data onto automated
PowerBI sheets
Attached is
the summary of the documentation of the measures and KPIs used for the client.
The purpose
of the project was as follows:
1.) Automate the reports using Excel and
Tally on the Powerbi web platform
2.) The above actions shall help the end
client in the following ways:
a. Automate the manual reports between
Tally and Excel onto the secure Azure cloud.
b. It shall also help the end client get
correct numbers by removing the row limitations from Excel. There can be errors
with regards to copying the data from Tally onto Excel.
The attached
document describes the measures that have been converted from the excel
spreadsheets onto the Powerbi DAX platform.
Attached is
the
1). Table:
30th Days
Below are
the measures of this Table that are calculated in Power BI using DAX coding:-
Measures:
a). Max
inventory level: Product of safety factor, lead time(no. of days) and 30th
day average quantity
b). Order to
book: If T/F is equal to ‘To Book’ then each row will have the value of
difference between Max Inventory Level and Item MOQ
The order to book is an aggregated measure that is calculated based on the following logic:
a.) If the 30thdayT/F is “To Book” then sum the difference between ‘30th days[Max inventory level]’ and ‘30th days [Item MOQ]’
Excel Formula for Order To Book column :
c). Percentage: Difference between “Total stock” and “Max inventory level” is divided by item MOQ to get “Percentage”
d). Stock
Qty. : This column is added by matching Name from X Inventory table with Item Name from 30th
day Table using Lookupvalue function.
The closing
balance value was picked up after a match between ‘stockitemname [X]’ and ‘30th
Days’ [stockitemname]’
Excel
Formula For the Stock Quantity is as follows : Here VLookUp function will
travel from shell A2 to shell A209 in 30th Days worksheet and from shell A2 to
shell B1836 in stocks worksheet and return value of Quantity to 'stock qty'
column if no error is there otherwise return nothing.
e). T/F : If
Max inventory level is greater than MOQ then T/F will be TO BOOK otherwise OK
If then else
is self explanatory.
f).Total
Stock : It is the Sum of Stock Qty., Transit Qty. and Pending Qty.
No comments:
Post a Comment