Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Sales by Product Category

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

Need Help
Show and Tell
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    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.

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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.

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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.