Sales by Product Category

[Anonymous]

Hi everyone, I'm a newbie so please excuse me if the questions seem trivial.

I have 2 tables containing product info and sales info. Firstly I want to create a bar chart showing the top selling(€ not units) product in each category. And secondly I want to create an alert/highlight the products that are on less than 3 weeks cover (based on stock and average weekly sales). How do I do this? with a Measure(which? I am not yet familiar with the M language) or are there options already in Power Bi for this? My column names are:

 Product Info Table: Product_Category, Product_Description, Product_Number, Stock

 Sales Info Table: Product_Number, Sale_Value, Quantity_Sold, Date

Many thanks!

Alexandra

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

1. top sales product every category

Create measures

total sales = SUM(Sale[sale unit])*SUM(Sale[quantity])

is top =
VAR rank_ =
    IF (
        [total sales]
            <> BLANK (),
        RANKX (
            FILTER (
                ALL ( 'Product'[Product] ),
                [total sales]
                    <> BLANK ()
            ),
            [total sales],
            ,
            DESC
        )
    )
RETURN
    IF (
        rank_ = 1,
        [total sales]
    )

2. whether stock is enough to sale for next 3 weeks.

Create

Create measures

count_week = CALCULATE(DISTINCTCOUNT('Sale'[year-week]),ALLEXCEPT(Sale,Sale[Product]))

average weekly =
VAR count_week =
    CALCULATE (
        DISTINCTCOUNT ( 'Sale'[year-week] ),
        ALLEXCEPT (
            Sale,
            Sale[Product]
        )
    )
RETURN
    [total sales] / count_week


next_3weeks_predict = [average weekly]*3

remaining stock = SUM('Product'[Stock])-[total sales]

is enough = [remaining stock]-[next_3weeks_predict]

highlight = IF([is enough]<0,1,0)

[year-week] is a calcualted column

year-week = FORMAT([date],"yyyy-ww")

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

Very much depends on how your data is structured. Please post example/sample of data. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

For the values, you might be able to get away with the default aggregations or you may need to do a calculated column or measure. It really depends on the data.

For the highlighting, you can use Conditional Formatting although it can be tricky to find sometimes.