Cost of going out of stock

[Chitemerere]

I have used R in Power BI to calculate the cost of going out of stock for a product item.  However, R visuals are not currrently supported in the web and I need to convert my R script to Power BI.  My R script for calcualating cost of stock out for a 30 day perod is as follows:

# dataset <- data.frame(UnitPrice, Date, Item, Quantity)

# dataset <- unique(dataset)

library(dplyr)

library(ggplot2)

stocks <- dataset %>%

group_by(Item) %>%

mutate(Avg_Unit_Price = mean(UnitPrice)) %>%

mutate(first_date = min(as.Date(Date)),

last_date = max(as.Date(Date)),

exposure = last_date - first_date) %>%

select(Item, Quantity, UnitPrice, exposure) %>%

mutate(exposure = as.numeric(exposure)) %>%

filter(exposure > 0) %>%

mutate(totalQuantity = sum(Quantity)) %>%

mutate(AUSPD = totalQuantity/exposure) %>%

distinct() %>%

mutate(stockOutValue = 30*AUSPD*UnitPrice) %>%

summarise(Avg_stockOut_Value = (mean(stockOutValue))) %>%

arrange(desc(Avg_stockOut_Value))

ggplot(data = stocks, aes(x=reorder(Item,Avg_stockOut_Value), y = Avg_stockOut_Value, fill = Item)) +

geom_bar(stat="identity") +

coord_flip()+

geom_label(aes(label= paste("$", format(Avg_stockOut_Value, big.mark = ","))))+

theme(legend.position="none")+

theme(panel.background = element_blank())+

theme(axis.text = element_text(size = 10)) +

labs(x = "Item", y = "Cost of Stockout", title = "Cost of Stock Outs by Item") +

labs(title="Cost of Stock Outs by Item")

The output is as follows:

Can the results of the R script be accomplished in Power BI?

Regards,

Chris

[v-lionel-msft]

Hi @Chitemerere ,

Yes. "R" Visuals are not currently supported in Publish to web reports.

You can use DAX measures and a stacked bar chart instead of R script.

If you can't create the measures, please give me the sample data and tell me what you want to calculate, such as sum, average. ..(I can't understand R code.).

Best regards,
Lionel Chen

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

[Chitemerere]

Dear Lionel

Thank you very much for offering to assist.  Please find below the raw data.

https://drive.google.com/file/d/1Wnqzrd2BmyijWPdMSAC1yDH92Nlcarza/view?usp=sharing 

The idea is to group the above table by "Item" and sort by "Item" and "Dates" columns in descending order and calculating the column "exposure" measured in days.  Exposure is the maximum "Dates" minus the minium "Dates" column for the particular "Item".  This is shown in the Excel sheet below where the calculation were done using Excel.

https://drive.google.com/file/d/1Z627BTYGtQ6VfsD9-8PzsD3Cf_Vv7yWd/view?usp=sharing 

The next step is to calculate AUSPD which is the Average Units Sold per Day as follows:

AUSPD = SUM(Quantity)/exposure

Stock Out Value for 30 days will be as follows:

StockOutValue = 30*AUSPD*UnitPrice

AverageStockOutValue = Average(StockOutValue)

Then plot "Item" versus "AverageStockOutValue as a bar chart

Best regards,

Chris

[v-lionel-msft]

Hi @Chitemerere ,

Has your problem been solved?

Best regards,
Lionel Chen

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

[Chitemerere]

My apologies for the late response, thank you very much, have accepted it as a solution.

Best regards,

Chris

[v-lionel-msft · Accepted solution]

Hi @Chitemerere ,

Please refer to the measures:

exposure = 
VAR x = 
CALCULATE(
    MAX(Sheet3[Dates]),
    ALLEXCEPT(Sheet3, Sheet3[Item])
)
VAR y = 
CALCULATE(
    MIN(Sheet3[Dates]),
    ALLEXCEPT(Sheet3, Sheet3[Item])
)
VAR x1 = 
CALCULATE(
    MAX(Sheet3[Quantity]) * MAX(Sheet3[UnitPrice]),
    FILTER(
        Sheet3,
        Sheet3[Dates] = x
    )
)
VAR y1 = 
CALCULATE(
    MAX(Sheet3[Quantity]) * MAX(Sheet3[UnitPrice]),
    FILTER(
        Sheet3,
        Sheet3[Dates] = y
    )
)
RETURN
CALCULATE(
    y1 - x1,
    ALL(Sheet3)
)

AUSPD = 
DIVIDE(
    SUM(Sheet3[Quantity]),
    [exposure]
)

StockOutValue = 30*[AUSPD]*MAX(Sheet3[UnitPrice])

AverageStockOutValue = 
AVERAGEX(
    Sheet3,
    Sheet3[StockOutValue]
)

Best regards,
Lionel Chen

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

[amitchandak]

@Chitemerere , Not sure I got it.

Refer: https://docs.microsoft.com/en-us/power-bi/desktop-r-visuals

https://dzone.com/articles/r-with-powerbi-a-step-by-step-guide

[Chitemerere]

Thank you very much for your response.  As explained in my post, i have successfully used R to calculated the cost of stock outs.  What i am looking for is an alternative solution which does not use R as curently my R visual is not support in web publishing.

Regards,

Chris