Sales by Product Category

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.