TOPN Rankx

[SHShagar]

Hi is there any way to show top counted values of a column by monthly basis?
like for every month/week i want the 5 top counted text values and the top should be calculated for every month/week.
Thanks in advance.

[v-juanli-msft · Accepted solution]

Hi @SHShagar 

It is possible.

As tested with my exmaple data, it can do as below:

Creaate a calcuated table and connect it to your table

calendar date =
ADDCOLUMNS (
    CALENDARAUTO (),
    "year", YEAR ( [Date] ),
    "month", MONTH ( [Date] ),
    "week", WEEKNUM ( [Date], 2 )
)

Create columns/measures in your table

column:
month = RELATED('calendar date'[month])

measure:
Measure =
CALCULATE (
    COUNT ( Sheet3[value] ),
    FILTER (
        ALLSELECTED ( Sheet3 ),
        Sheet3[value] = MAX ( Sheet3[value] )
            && Sheet3[month] = MAX ( Sheet3[month] )
    )
)


Measure 2 = RANKX(FILTER(ALLSELECTED(Sheet3),Sheet3[month]=MAX(Sheet3[month])),[Measure],,DESC,Dense)

add measure2 in visual level filter

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.