Forum Discussion

SHShagar's avatar
SHShagar
New Member
7 years ago
Solved

TOPN Rankx

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.

Tutorial Requests
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    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.

1 Reply

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    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.