Annual sum ranking of products

[CCmuffin]

Hi, I've been stuck with this issue for a few weeks.

I am making an annual search sum ranking of products.

There are dozens of categories and thousands of products.

I record the number of searches and rankings of all products every month.

There is also another table that categorizes all products.

I want to get a ranking of the category every month, with data from that point to 1 year before then.

There are two criteria.

1. Every month, check if there is at least one product in rank 200 or more in each category.

 The number of times per year is summed up. It will be up to 12 times.

2. By category, add up all the top 200 searches in the past year.

I have no idea how to start. Please help.

[v-zhangti]

Hi, @CCmuffin 

Based on your description, I briefly simulated some data.
1. Every month, check if there is at least one product in each category that is ranked 3 or higher.
2. By category, add up all the top 3 searches in the past year.

Please check if it meets your needs, and reply to inform if you have additional requests.

Measure =
CALCULATE (
    COUNT ( 'Table'[Product] ),
    FILTER (
        ALL ( 'Table' ),
        [rank] <= 3
            && [month] = SELECTEDVALUE ( 'Table'[month] )
            && [category] = SELECTEDVALUE ( 'Table'[category] )
    )
)

Measure2 =
VAR N1 =
    CALCULATE (
        SUM ( 'Table'[searches] ),
        FILTER (
            ALL ( 'Table' ),
            [category] = SELECTEDVALUE ( 'Table'[category] )
                && [rank] <= 3
        )
    )
VAR N2 =
    CALCULATE (
        N1,
        DATESINPERIOD ( 'Table'[Date], LASTDATE ( 'Table'[Date] ), -12, MONTH )
    )
RETURN
    N2

Please check the attachment.

Best Regards,

Community Support Team _Charlotte

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

[CCmuffin]

Wow. It's pretty close to what I wanted.

but I need a little modification.

Measure = 
CALCULATE (
    COUNTA( 'Data'[product]),
    FILTER (
        ALL ( 'Data'),
        [rank] <= 200
            && [date] = DATESINPERIOD ('Data'[date], LASTDATE ( 'Data'[date] ), -12, MONTH )
            && [category] = SELECTEDVALUE ( 'Data'[category] )
    )
)

Measure 1 is to add +1 to the category if any of the products are in the top 200.

So I changed 'COUNT' to 'COUNTA' and also limited the period in an year.

Why is this code not working?

I attatched my BI file.

https://1drv.ms/u/s!AjtR6aAtuhbt_HNH08m1B9sxze6X?e=fJocoY

thank you.

[Jihwan_Kim]

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

All measures are in the attached pbix file.

Number of search: =

SUM('Search'[Search] )

Number of search last 12 months: =

CALCULATE (

[Number of search:],

DATESINPERIOD ( Dates[Date], LASTDATE ( Dates[Date] ), -12, MONTH )

)

Number of search by product only top 5: =

VAR categorymonthtable =

FILTER (

SUMMARIZE (

ALL ( 'Search' ),

Dates[Month & Year],

'Product'[Category],

'Product'[Product]

),

Dates[Month & Year] = MAX ( Dates[Month & Year] )

)

RETURN

CALCULATE (

[Number of search last 12 months:],

KEEPFILTERS (

TOPN ( 5, categorymonthtable, [Number of search last 12 months:], DESC )

)

)

[CCmuffin]

This is not what I was looking for, but it's a good example to practice. I learned a lot from this.
The keymeasures trick is amazing. It will be useful.