DAX: show top N values by category

[Martynas_BI]

Hi,

I would like to show the top 3 company tickers based on 'Weight, %' column.

I would also like this calculation to be responsive to a 'report view' filter, when filtering by year (i.e. top 3 companies in year 2022 will be different than in 2021, etc.)

Here is the full data set if needed:

https://docs.google.com/spreadsheets/d/1Sa9xva8HVuW7178WPJPV-nv0ClrqMwDM19SokaIztMM/edit#gid=0

Many thanks in advance

[Jihwan_Kim]

Hi,

I don't know how you want to model your data, but I assume your datamodel looks something like below.

I saw that there are two companies who has two Ticker categories. Please correct me if I misunderstood.

Top3 weight percent measure: =
CALCULATE (
    DIVIDE ( SUM ( Data[Shares] ), SUM ( Data[Value] ) ),
    KEEPFILTERS (
        WINDOW (
            1,
            ABS,
            3,
            ABS,
            ALLSELECTED ( Ticker[Ticker] ),
            ORDERBY (
                CALCULATE ( DIVIDE ( SUM ( Data[Shares] ), SUM ( Data[Value] ) ) ), DESC
            )
        )
    )
)